VLOOKUP matching on more than one condition

[JadonR]

Hey guys. I've used VLOOKUP's in the past but this seems a bit more complicated than that. Essentially I would like to use a formula to populate Column 'H' on the sheet named UPCS Export (see highlighted field in first image below) with the value in the 'K' column from the IM_BARCOD sheet (see second image below). The difficult part is that it needs to also match on two other columns. The values in UPCS Export, Column 'B' should match the number in IM_BARCOD, Column A. It should also match UPCS Export, Column 'E' on IM_BARCOD, Column 'C'. If there is NO MATCH, then the number in Column H should be incremented in sequence based on the highest number. See last image for more details.

So below you can see that there is a match between the first 5 rows (non-highlighted in below image). The match should be connected between sheet IM_BARCOD, Column A and sheet UPCS Export Column B. It also needs to match between IM_BARCOD, Column C and sheet UPCS Export Column E.

The next 10 do NOT MATCH (highlighted) so those should look at the highest value in sheet IM_BARCOD, Column K for the corresponding range of numbers in Column A. In the example below Sheet IM_BARCOD has number 38518 in Column Awith the highest sequencial number ending in 22. So it should assign 23 through 32.

I didn't even attempt a VLOOKUP as I know I couldn't come close. Any help would be appreciated.

 

[awoohaw]

I did not type JadenR in the solution I sent, (and it is my impression excel should not have captured the local sheet name when people are using the mouse to select cells for formulas (which is what I did)). JadonR is what I named the worksheet I was working in. You could change that to 'UPCS Export'
in the formula but that is BAD excel for (why I'm so upset in my first parenthetical statement above)

try this in your cell H2 of your UPCS Export worksheet:

=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(B3&E3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64,0))
=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(B3&E3&F3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))

this should not have any JadonR sheet references:

mr excel questions 24.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	BARCOD	ITEM_NO	BARCOD_ID	UNIT	DIM_1_UPR	DIM_2_UPR	DIM_3_UPR		SEQ_NO
2	=Table1[@UPC]	=Table1[@[Style Number]]	UPC	#REF!	=VLOOKUP(Table1[@[Color Code]],'Color Codes'!A$3:B$1000,2,FALSE)	=Table1[@Size]	=IF(Table1[@[Alt Size]]=0,"",Table1[@[Alt Size]])		1	triple match (added size lookup)
3	192964204546	38518	UPC	0	WHI-White	M		1	2	3
4	192964204577	38518	UPC	0	WHI-White	L		1	3	4
5	192964204607	38518	UPC	0	WHI-White	XL		1	4	5
6	192964204638	38518	UPC	0	WHI-White	XXL		1	5	6
7	195699786844	38518	UPC	0	BURD-Burl Red	S		#N/A	6	#N/A
JadonR

Cell Formulas
Range	Formula
D2	D2	='Color Codes'!C1
H3:H7	H3	=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(B3&E3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64,0))
J3:J7	J3	=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(B3&E3&F3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))

 

[JadonR]

Hmm. Very strange. I copied the formula exactly and get a #VALUE! formula error. See below.

 

[awoohaw]

Hmm. Very strange. I copied the formula exactly and get a #VALUE! formula error. See below.

View attachment 89875

make sure that the cell formats of columns
IM_BARCOD!A and 'UPCS Export'B matcch
IM_BARCOD!E and 'UPCS Export'C match
IM_BARCOD!D and 'UPCS Export'!F match

 

[JadonR]

I changed all cell formats in both tabs to "text". Error remains. Not sure why this is being so difficult for me...

 

[JadonR]

So, pressing F2 to edit the formula, then pressing CTRL+SHIFT+ENTER makes it work. Not sure why but that did it.

It changed the formula to:

{=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(B3&E3&F3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))}

 

[awoohaw]

So, pressing F2 to edit the formula, then pressing CTRL+SHIFT+ENTER makes it work. Not sure why but that did it.

It changed the formula to:

{=INDEX(IM_BARCOD!$K$2:$K$64,MATCH(B3&E3&F3,IM_BARCOD!$A$2:$A$64&IM_BARCOD!$C$2:$C$64&IM_BARCOD!$D$2:$D$64,0))}

that is odd. it is not an array formula function, and you are using 365 where CSE entry is not required for arrays.
all the braces mean is that it was entered using CSE.

And you should not change your data to text if you don't want to have text.

but, I'm happy you got it working.

Also, what happens if you hit F2 again and then hit enter?

Additionally, the formula i gave only goes to row 64.. you need to change the 64 to fit all of you column lengths.

 

[JadonR]

Thanks. I updated the formula to use 10000 instead of 64. However, there is one more question. If there is no match, it shows #N/A. Instead, I would like the formula to insert a number that will follow the sequence of numbers from the IM_BARCOD sheet that is next in line for that item number. In other words, below you can see in yellow the values that it should be filling in with.

CP Export Datasheet 1st t-shirt PO colors added.xlsm
	A	B	C	D	E	F	G	H	I
1	BARCOD	ITEM_NO	BARCOD_ID	UNIT	DIM_1_UPR	DIM_2_UPR	DIM_3_UPR	SEQ_NO
2	192964204515	38518	UPC	0	WHI-White	S	Grid3	2
3	192964204546	38518	UPC	0	WHI-White	M		3
4	192964204577	38518	UPC	0	WHI-White	L		4
5	192964204607	38518	UPC	0	WHI-White	XL		5
6	192964204638	38518	UPC	0	WHI-White	XXL		6
7	195699786844	38518	UPC	0	BURD-Burl Red	S		#N/A	62
8	195699786899	38518	UPC	0	BURD-Burl Red	M		#N/A	63
9	195699786967	38518	UPC	0	BURD-Burl Red	L		#N/A	64
10	195699787056	38518	UPC	0	BURD-Burl Red	XL		#N/A	65
11	195699787148	38518	UPC	0	BURD-Burl Red	XXL		#N/A	66
12	195699794160	38504	UPC	0	DMGO-Dri Mango	S		#N/A
13	195699794269	38504	UPC	0	DMGO-Dri Mango	M		#N/A
14	195699794337	38504	UPC	0	DMGO-Dri Mango	L		#N/A
15	195699794405	38504	UPC	0	DMGO-Dri Mango	XL		#N/A
UPCS Export

Cell Formulas
Range	Formula
A2	A2	=Table1[@UPC]
D2	D2	='Color Codes'!C1
E2	E2	=VLOOKUP(Table1[@[Color Code]],'Color Codes'!A$3:B$1000,2,FALSE)
F2	F2	=Table1[@Size]
G2	G2	=IF(Table1[@[Alt Size]]=0,"",Table1[@[Alt Size]])
B2:B15	B2	=Table1[@[Style Number]]
H2:H15	H2	=INDEX(IM_BARCOD!$K$2:$K$10000,MATCH(B2&E2&F2,IM_BARCOD!$A$2:$A$10000&IM_BARCOD!$C$2:$C$10000&IM_BARCOD!$D$2:$D$10000,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

Here you can see where the range of sequence numbers stops on the IM_BARCOD sheet for this item number. So it should pick up from 61 by inserting 62 and so on for all rows that don't have a match.

 

[awoohaw]

Thanks. I updated the formula to use 10000 instead of 64. However, there is one more question. If there is no match, it shows #N/A. Instead, I would like the formula to insert a number that will follow the sequence of numbers from the IM_BARCOD sheet that is next in line for that item number. In other words, below you can see in yellow the values that it should be filling in with.

CP Export Datasheet 1st t-shirt PO colors added.xlsm
	A	B	C	D	E	F	G	H	I
1	BARCOD	ITEM_NO	BARCOD_ID	UNIT	DIM_1_UPR	DIM_2_UPR	DIM_3_UPR	SEQ_NO
2	192964204515	38518	UPC	0	WHI-White	S	Grid3	2
3	192964204546	38518	UPC	0	WHI-White	M		3
4	192964204577	38518	UPC	0	WHI-White	L		4
5	192964204607	38518	UPC	0	WHI-White	XL		5
6	192964204638	38518	UPC	0	WHI-White	XXL		6
7	195699786844	38518	UPC	0	BURD-Burl Red	S		#N/A	62
8	195699786899	38518	UPC	0	BURD-Burl Red	M		#N/A	63
9	195699786967	38518	UPC	0	BURD-Burl Red	L		#N/A	64
10	195699787056	38518	UPC	0	BURD-Burl Red	XL		#N/A	65
11	195699787148	38518	UPC	0	BURD-Burl Red	XXL		#N/A	66
12	195699794160	38504	UPC	0	DMGO-Dri Mango	S		#N/A
13	195699794269	38504	UPC	0	DMGO-Dri Mango	M		#N/A
14	195699794337	38504	UPC	0	DMGO-Dri Mango	L		#N/A
15	195699794405	38504	UPC	0	DMGO-Dri Mango	XL		#N/A
UPCS Export

Cell Formulas
Range	Formula
A2	A2	=Table1[@UPC]
D2	D2	='Color Codes'!C1
E2	E2	=VLOOKUP(Table1[@[Color Code]],'Color Codes'!A$3:B$1000,2,FALSE)
F2	F2	=Table1[@Size]
G2	G2	=IF(Table1[@[Alt Size]]=0,"",Table1[@[Alt Size]])
B2:B15	B2	=Table1[@[Style Number]]
H2:H15	H2	=INDEX(IM_BARCOD!$K$2:$K$10000,MATCH(B2&E2&F2,IM_BARCOD!$A$2:$A$10000&IM_BARCOD!$C$2:$C$10000&IM_BARCOD!$D$2:$D$10000,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

Here you can see where the range of sequence numbers stops on the IM_BARCOD sheet for this item number. So it should pick up from 61 by inserting 62 and so on for all rows that don't have a match.
View attachment 89946

wrap the formula in an "IFERROR" function, with the 3rd argument being the cell address above + 1. So, if in cell H2:
=IFERROR('the formula', N(H1)+1)

Note: if cell H1 is non numeric, the N() function changes non numeric values to 0.

 

[awoohaw]

wrap the formula in an "IFERROR" function, with the 3rd argument being the cell address above + 1. So, if in cell H2:
=IFERROR('the formula', N(H1)+1)

Note: if cell H1 is non numeric, the N() function changes non numeric values to 0.

i just realized you don't want the cell above plus 1.

Your example has a conundrum. How would you know what the record above matches too? In the example, how I am to know to look for 38518, WAVY BLUE XXL to find the number to add 1 to? (Sorry if misspelled, again, images are not helpful).

 

[JadonR]

Sorry for the images. Couldn't it be some additional nested match that could look at the item no and then the highest seq no for that item no? Then increment by 1. But it sounds like that may be out of reach?