Finding values using 2 cells as criteria searching through multiple sheets

[NSegna]

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.

Thank you in advance.

 

[RasGhul]

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
4. Repeat above for Shoes/Shoesheading & Pants/Pantsheading

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

Book1
	A	B	C	D	E
2	Item	Source	Source2	Store	Price
3	T-shirt	Shirts	ShirtsHeading	American Eagle	8
4	Shorts	Pants	PantsHeading	Gap	20
5	Sneakers	Shoes	ShoesHeading	Adidas	60
6	Jeans	Pants	PantsHeading	Levis	45
7	Polo	Shirts	ShirtsHeading	Calvin Klein	35
8	Jersey	Shirts	ShirtsHeading	Nike	60
Sheet1

Cell Formulas
Range	Formula
C3	=B3&"Heading"
E3	=VLOOKUP(A3,INDIRECT(B3),MATCH(D3,INDIRECT(C3),0),0)