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]

no need to apologize. One of my life mantras is "Go for the know!"... an unanswered question never gets a "YES". If you ask and get a 'no', you KNOW.

However, I was thinking .... what you could do is remove the size criteria lookup and if you get a 'hit' that could help. But it requires that all columns are sorted in order. You can try to play around with MATCH or XMATCH to see if you can get the maximum. There are probably some other fairly intense ways of using array formulas as well. I wish I could be more specific.