Author: Oscar Cronquist Article last updated on August 29, 2017

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

Recommended reading

Add new items to a drop down list automatically

A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you […]

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))), "")

Learn to construct a formula that returns multiple values sorted alphabetically:

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

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 enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

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:

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Download excel *.xlsx file