Multiple vlookups in a nested if statement

[Trabo86]

I am trying to take a list of Product Numbers in Table 1 and put a Type description next to it based on which column the item number appears in a separate table. I can get the separate IF/Vlookup statements to work individually, but not all together.
Formula I'm using that doesn't work:
=IF(A3=VLOOKUP(A3,'Table 2'!$A$10:$A$11,1,FALSE),"PROMO",(IF(A3=VLOOKUP(A3,'Table 2'!$C$2:$C$16,1,FALSE),"BURNDOWN","STOCK")))

Example of data:

Table 1
ProdNbr	Desc	Type
97609	Peanuts	PROMO
97602	Popcorn
97415	Soda
97451A	Licorice
Table 2
Promo	Stock	Burndown
97609	97602	97415
97451A	97425	97599

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

Any help would be much appreciated!

 

[jtakw]

Hi,

If I understand your description correctly, I don't think you need VLOOKUP for this.

One question thou, will there be instances where a Product Number in Table 1 has No match in Table 2 at all?

Book1
	A	B	C
1	Table 1
2	ProdNbr	Desc	Type
3	97609	Peanuts	PROMO
4	97602	Popcorn	STOCK
5	97415	Soda	BURNDOWN
6	97451A	Licorice	PROMO
7
8
9	Table 2
10	Promo	Stock	Burndown
11	97609	97602	97415
12	97451A	97425	97599
Sheet139

Cell Formulas
Range	Formula
C3	=IF(COUNTIF(A$11:A$12,A3),"PROMO",IF(COUNTIF(B$11:B$12,A3),"STOCK","BURNDOWN"))

C3 formula copied down.

 

[Aladin Akyurek]

Book1
	A	B	C
1	ProdNbr	Desc	Type
2	97609	Peanuts	Promo
3	97602	Popcorn	Stock
4	97415	Soda	Burndown
5	99999	mulberry	not available
6	97451A	Licorice	Promo
Sheet1

Book1
	A	B	C
1	Promo	Stock	Burndown
2	97609	97602	97415
3	97451A	97425	97599
Sheet2

In C2 of Sheet1 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS(Sheet2!$A$2:$C$3,$A2),INDEX(Sheet2!$A$1:$C$1,MIN(IF(Sheet2!$A$2:$C$3=$A2,COLUMN(Sheet2!$A$1:$C$1)-COLUMN(Sheet2!$A$1)+1))),"not available")

 

[Trabo86]

jtakw, To answer your question, there should not be a time where there is no match. I tried your formula and it worked beautifully! Thank you.

Aladin, I had trouble getting the array to work, but I'm good with the other formula. Thank you both very much.

 

[Aladin Akyurek]

jtakw, To answer your question, there should not be a time where there is no match. I tried your formula and it worked beautifully! Thank you.

Aladin, I had trouble getting the array to work, but I'm good with the other formula. Thank you both very much.

See https://www.dropbox.com/s/qnlho79vang696o/Trabo86 type determination.xlsx?dl=0

Control+shift+enter; Press down the shift and the control keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.