Validation and Lookup
in [General]
|
From: max007 on 8 Mar 2010 20:43 Im am creating a Price Inquiry tool... I am just having problems when it comes to the lookup for the prices... I have A1 as the Item Category drop down and B1 as the Item drop down... I want C3 to return the price of the item once B1 already has the exact item to lookup... I created the A1 and B1 as dependent drop down list. A B C 1 Gardening Water Hose Price Need help.. Please advise
From: ck on 8 Mar 2010 21:00 From what i understand is that you want column C to return the price of the item if column A is selected and column B, you select 'price' from the drop down list. If so, assuming in your database, you have set column E for all the items and column F for all the prices for those items, in column C, try this formula: =IF(B2="Price",INDEX($F$2:$F$4,MATCH(D2,$E$2:$E$4,0)),"") Change the range as accordingly. Click yes below if this is what you want. "max007" wrote: > Im am creating a Price Inquiry tool... I am just having problems when it > comes to the lookup for the prices... > > I have A1 as the Item Category drop down and B1 as the Item drop down... I > want C3 to return the price of the item once B1 already has the exact item to > lookup... > > I created the A1 and B1 as dependent drop down list. > > A B C > 1 Gardening Water Hose Price > > > Need help.. Please advise >
From: ck on 8 Mar 2010 21:05 Sorry should be =IF(B2="Price",INDEX($F$2:$F$4,MATCH(A2,$E$2:$E$4,0)),"") "ck" wrote: > From what i understand is that you want column C to return the price of the > item if column A is selected and column B, you select 'price' from the drop > down list. If so, assuming in your database, you have set column E for all > the items and column F for all the prices for those items, in column C, try > this formula: > > =IF(B2="Price",INDEX($F$2:$F$4,MATCH(D2,$E$2:$E$4,0)),"") > > Change the range as accordingly. > > Click yes below if this is what you want. > > > "max007" wrote: > > > Im am creating a Price Inquiry tool... I am just having problems when it > > comes to the lookup for the prices... > > > > I have A1 as the Item Category drop down and B1 as the Item drop down... I > > want C3 to return the price of the item once B1 already has the exact item to > > lookup... > > > > I created the A1 and B1 as dependent drop down list. > > > > A B C > > 1 Gardening Water Hose Price > > > > > > Need help.. Please advise > >
From: max007 on 8 Mar 2010 21:27 "ck" wrote: > Sorry should be =IF(B2="Price",INDEX($F$2:$F$4,MATCH(A2,$E$2:$E$4,0)),"") > > > "ck" wrote: > > > From what i understand is that you want column C to return the price of the > > item if column A is selected and column B, you select 'price' from the drop > > down list. If so, assuming in your database, you have set column E for all > > the items and column F for all the prices for those items, in column C, try > > this formula: > > > > =IF(B2="Price",INDEX($F$2:$F$4,MATCH(D2,$E$2:$E$4,0)),"") > > > > Change the range as accordingly. > > > > Click yes below if this is what you want. > > > > > > "max007" wrote: > > > > > Im am creating a Price Inquiry tool... I am just having problems when it > > > comes to the lookup for the prices... > > > > > > I have A1 as the Item Category drop down and B1 as the Item drop down... I > > > want C3 to return the price of the item once B1 already has the exact item to > > > lookup... > > > > > > I created the A1 and B1 as dependent drop down list. > > > > > > A B C > > > 1 Gardening Water Hose Price > > > > > > > > > Need help.. Please advise maybe just to add some detail... A B C 1 Price 2 Gardening Water Hose 3 4 Gardening Hardware Price 5 Water Hose Nails 100 6 Fertizer Hammer 50 > > >
From: ck on 8 Mar 2010 21:48 Sorry your example all jumble up and I cannot decipher. Am i correct that you need to find the price (column C) based on the selection in column A and column B? If so, you can try to use SUMPRODUCT for your 2 criteria. How does your database look like? "max007" wrote: > > > "ck" wrote: > > > Sorry should be =IF(B2="Price",INDEX($F$2:$F$4,MATCH(A2,$E$2:$E$4,0)),"") > > > > > > "ck" wrote: > > > > > From what i understand is that you want column C to return the price of the > > > item if column A is selected and column B, you select 'price' from the drop > > > down list. If so, assuming in your database, you have set column E for all > > > the items and column F for all the prices for those items, in column C, try > > > this formula: > > > > > > =IF(B2="Price",INDEX($F$2:$F$4,MATCH(D2,$E$2:$E$4,0)),"") > > > > > > Change the range as accordingly. > > > > > > Click yes below if this is what you want. > > > > > > > > > "max007" wrote: > > > > > > > Im am creating a Price Inquiry tool... I am just having problems when it > > > > comes to the lookup for the prices... > > > > > > > > I have A1 as the Item Category drop down and B1 as the Item drop down... I > > > > want C3 to return the price of the item once B1 already has the exact item to > > > > lookup... > > > > > > > > I created the A1 and B1 as dependent drop down list. > > > > > > > > A B C > > > > 1 Gardening Water Hose Price > > > > > > > > > > > > Need help.. Please advise > > > > > maybe just to add some detail... > > A B C > 1 Price > 2 Gardening Water Hose > 3 > 4 Gardening Hardware Price > 5 Water Hose Nails 100 > 6 Fertizer Hammer 50 > > > > > > > > >
|
Next
|
Last
Pages: 1 2 Prev: why is autofilter not available (in light grey on drop down) Next: Formula |