# Finding values using 2 cells as criteria searching through multiple sheets

#### NSegna

##### New Member
Hello,
I was wondering if there is a function that can help me out with the following,

 Item Store Price T-shirt American Eagle Shorts Gap Sneakers Adidas Jeans Levis Polo Calvin Klein Jersey Nike Sheet 1

<colgroup><col width="65" style="width: 65pt;"><col width="92" style="width: 92pt;"><col width="65" style="width: 65pt;"></colgroup><tbody>
</tbody>
 Shirts American Eagle Gap Nike Calvin Klein T-shirt 8 10 15 20 Long sleeve 12 13 15 30 Polo 15 25 25 35 Dress 30 30 0 50 Button-up 20 25 0 45 Jersey 0 0 60 0 Graphic 15 15 20 20

<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody>
</tbody>
Sheet 2

 Pants Gap Levis Jack & Jones Shorts 20 25 30 Jeans 35 45 50 Joggers 0 35 30 Dress 60 70 75

<colgroup><col width="65" span="4" style="width: 65pt;"></colgroup><tbody>
</tbody>
Sheet 3

 Shoes Adidas Aldo Reebok Nike Sneaker 60 0 45 50 Running 55 40 35 45 Hi-Top 90 0 0 75 Dress 0 100 0 0

<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody>
</tbody>
Sheet 4

I was wondering if there is a formula to enter in cell C2 that can look at the values in A2 and B2 and then find the sheet that has both values in it and use A2 to find the row and B2 to find the column and then put the corresponding price in the C2 cell.

If possible please give a formula rather than a macro/VBA.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### RasGhul

##### Well-known Member
Hi,

This worked for me;

1. Select a cell from Shirts & convert to a table with CTRL-T
2. While the table is highlighted give the table a named range called Shirts
3. Then Highlight the whole header row for Shirts, the create named range called shirtsheading

This will be dynamic and you'll be able to add rows and columns to each of your tables without updating your vlookup;

Replies
6
Views
316
Replies
5
Views
349
Replies
3
Views
248
Replies
6
Views
511
Replies
4
Views
58