Lookup/find value with multiple criteria from multiple named ranges/tables

**sshahils** · 05-10-2012, 06:29 AM

example.xlsx

Hi,

I have set of around 20 different sized tables (in one sheet), from which i would like to automatically look up pricing info. Each table refers to a different product.

Within each table there are 3 different criteria.

- the first criteria is a quantity range to select the column within the table. The ranges differ in each table.
(e.g. if you want to order between 0-49 Apples, say 25, the price will be in column D and in the Apples table, etc).

- the next 2 criteria select the properties of the product and so the rows within the table.

In the attached worksheet i have set up 2 examples with the criteria and the values they should look up (highlighted).

Any help with this would be greatly appreciated (I am not against changing the format of the tables - i didn't set them up this way in the first place!). I know methods for the row selection, but the column selection (solving the range problem) and table selection are giving me issues.

regards,
Shahil

**JBeaucaire** · 05-10-2012, 10:24 AM

Use named ranges to make this simple.

1) Select the cells D16:J21 (the values) and name these cells "Apple" by typing into the Name box

http://screencast.com/t/Aqyyp4fetiK

2) Change the top row to indicate just the START of each tier for Qty.
3) Select the Qty cells and name these cells "AppleQty" by typing into the Name box

http://screencast.com/t/X3FuUjLxpq1

4) Select C16:C21 (the types) and name these cells "AppleType" by typing into the Name box

http://screencast.com/t/5UodEnu7xp

5) Now put this formula in B2:

=INDEX(INDIRECT(C3), MATCH(C4&" "&C5, INDIRECT(C3&"Type"), 0), MATCH(C6, INDIRECT(C3&"Qty"), 1))

6) Repeat that process with the ORANGE section, then just copy B2 and paste into I2.

http://screencast.com/t/3J4D5kD9Fp1d