Lookup Values based on Dropdown Selection on other Sheets.

[daleholden]

HI Guys

I am trying to work out how how i get different lookup values based on a selection made in a drop down box.

My data is on different sheets. Basically what ever i select in the drop down on cell E on Sheet Sales should determines what Vlookup returns and which sheet it goes to for its lookup data.

See workbook HERE you may need to edit in Excel desktop to see drop down

Also you can download it HERE

 

[carteBlanche]

Hi dale,

1. Make sure your list holds the exact same names than the worksheets name ("eBay", "Amazon", "WebsiteUK").

2. Make sure the structure of each merchant worksheet is identical with identical headers (replace "eBay Price" or "Amazon Price" with "Price" on each worksheet for instance). Depending on your formula, this is not necessary but I think it is a good practice anyway

3. You can use the following formula:

=INDEX(INDIRECT("'"&$E2&"'!a2:z100");
MATCH($A2;INDIRECT("'"&$E2&"'!a2:a100");0);
MATCH(H$1;INDIRECT("'"&$E2&"'!a1:z1");0))

I don't like VLOOKUP, but here is what it'd look like:

=VLOOKUP($A3;INDIRECT("'"&$E3&"'!a:z");4)

Let me know if it does the trick.

Cheers,

Tom.

 

[LindenWolf]

Me personally I would name my ranges like, EBAY, Amazon, etc... then do an if statement that points to the drop down box..

SO for example If you named the amazon Range from A2:G2 (as an example) then all you would need to do is
Price=if(E2="Amazon",vlookup(A2,amazon,4,false),if(e2="Ebay",vlookup(a2,ebay,4,false),etc.....)))

 

[carteBlanche]

I think that INDEX/INDIRECT/MATCH is slightly more flexible than your option Linden (I admit that it can result in poor calculation performance with very large data set, though). What if you need to look up not only price data but also some other data? It may well get painful to name and maintain many ranges.

However, If I were to use your method Linden, I would format each data sets as named tables just to add a little bit more flexibility when you name ranges like ebay, amazon, etc.

 

[daleholden]

I think that INDEX/INDIRECT/MATCH is slightly more flexible than your option Linden (I admit that it can result in poor calculation performance with very large data set, though). What if you need to look up not only price data but also some other data? It may well get painful to name and maintain many ranges.

However, If I were to use your method Linden, I would format each data sets as named tables just to add a little bit more flexibility when you name ranges like ebay, amazon, etc.

Hi Guys

I need maximum flexibility for my solution and would like to know the best approach if possible without causing an argument

Dale

 

[carteBlanche]

I'd go with INDEX/INDIRECT/MATCH for maximum flexibility.

I hope I'm not going to cause an argument :D

 

[daleholden]

I'd go with INDEX/INDIRECT/MATCH for maximum flexibility.

I hope I'm not going to cause an argument :D

HI

I can see you have added some formulas but it is not working for ebay or Amazon

See here


Dale

 

[LindenWolf]

I agree with Carte..Vlookup will eat up some space.. I've always been more proficient with Vlookup so that is always my go to for quick response.. Another thing to consider is the scope size of your needs..Vlookup to me considers a small size spreadsheet.... But always more than 1 way to skin a cat...

 

[daleholden]

Hi Linden


I only put in a small snippet of my actual sheet that has 1000's of entries with many more columns working out Paypal Standard Fees Merchant Fess Crossborder Fess so a lot going on. Also all my sheets are tables i perhaps should have mentioned that .

I need an efficient flexible way to do it



Dale

 

[carteBlanche]

Hey Dale,

The reason is that there is an extra white space at the end of "eBay" and "Amazon" in your list. By contrast, there is no such white space in the worksheets name.

The names in the list must be exactly the same than the worksheets name (after removing the white spaces, make sure to re-select the name you want in the dropdown list).