Ainslie asks:

I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles "ON Work" The drop down menu is in cell B50
I then need to be able to look up whatever option is picked in B50 and look up mulitple values from a different worksheet entiled "Panels" the Panels table goes from A1:F80
So if i choose the option 140XL in B50 below it I need all the information from the "panels" sheet that corresponds with 140XL.

The Panels Sheet information is set up like this:
140XL 12025 16" Large
140XL 58625 12" Small
140XL 99951 12" Rear

So i need it to look up 140XL and get the infromation 12025,58625,9951

i have looked all over the internet for help and it's either way too confusing or doesn't deal with multiple worksheets. I know it is possible but i really am out of ideas


Create drop down list

  1. Go to "Data" tab
  2. Click "Data Validation" button
  3. Select List
  4. Type 140XL, 150XL, 160XL in "Source:" window
  5. Click OK

Excel 2007 array formula in cell B5:

=IFERROR(INDEX(Panels!$B$2:$B$7, SMALL(IF($B$2=Panels!$A$2:$A$7, MATCH(ROW(Panels!$A$2:$A$7), ROW(Panels!$A$2:$A$7)), ""), ROW(A1))), "")

How to enter an array formula

  1. Copy (Ctrl + c) above array formula
  2. Select cell B5
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift
  5. Press Enter once
  6. Release all keys

How to copy array formula

  1. Select cell B5
  2. Copy (Ctrl + c)
  3. Select cell range B7:B16
  4. Paste (Ctrl + v)

How this formula works

You can find an explanation here:
How to return multiple values using vlookup

Download excel *.xlsx file