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,

[TABLE="width: 222"]
<colgroup><col width="65" style="width: 65pt;"><col width="92" style="width: 92pt;"><col width="65" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 92"][/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="class: xl64"]Item[/TD]
[TD="class: xl64"]Store[/TD]
[TD="class: xl64"]Price[/TD]
[/TR]
[TR]
[TD="class: xl63"]T-shirt[/TD]
[TD="class: xl63"]American Eagle[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]Shorts[/TD]
[TD="class: xl63"]Gap[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]Sneakers[/TD]
[TD="class: xl63"]Adidas[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]Jeans[/TD]
[TD="class: xl63"]Levis[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]Polo[/TD]
[TD="class: xl63"]Calvin Klein[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]Jersey[/TD]
[TD="class: xl63"]Nike[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD]Sheet 1

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 325"]
<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 65"]Shirts[/TD]
[TD="width: 65"]American Eagle[/TD]
[TD="width: 65"]Gap[/TD]
[TD="width: 65"]Nike[/TD]
[TD="width: 65"]Calvin Klein[/TD]
[/TR]
[TR]
[TD]T-shirt[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Long sleeve[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Polo[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Dress[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Button-up[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Jersey[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Graphic[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2

[TABLE="width: 260"]
<colgroup><col width="65" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 65"]Pants[/TD]
[TD="width: 65"]Gap[/TD]
[TD="width: 65"]Levis[/TD]
[TD="width: 65"]Jack & Jones[/TD]
[/TR]
[TR]
[TD]Shorts[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Jeans[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Joggers[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Dress[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]75[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 3

[TABLE="width: 325"]
<colgroup><col width="65" span="5" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="width: 65"]Shoes[/TD]
[TD="width: 65"]Adidas[/TD]
[TD="width: 65"]Aldo[/TD]
[TD="width: 65"]Reebok[/TD]
[TD="width: 65"]Nike[/TD]
[/TR]
[TR]
[TD]Sneaker[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Running[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Hi-Top[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]Dress[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]
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)