Lookup Help - 2 Criteria Lookup with the 2nd Criteria being a range - Excel 2016

[Johnny Thunder]

Hello all,

I need help with a lookup and not sure how to achieve this.

My dilemma:

I need a 2 way look done on a row by row basis, similarly how a Vlookup would work except a Vlookup is done on a one criteria method.

Sheet1 = PO Details
Sheet2 = PO database


Row to Enter Formula: Sheet1.Cell (H2)
Criteria To Match 1: Cell Sheet1 (C2) = Vendor Name
Criteria To Match 2: Cell Sheet1 (E2) = Title

Lookup Criteria 1: Sheet2 (C2) = Vendor Name
Lookup Criteria 2: Need to search E2:P2 for Value if it matches Criteria from Criteria 2 (Title)
Return Value: Sheet2 (B2) = Title Category

The main issue that I am having is that sometimes my PO database has 1 line for vendor but that line touches 5 Titles and the titles are displayed in the same row Sheet2.Columns(E:P) but my PO Details may only need to have data from 1 of the titles.

Sheet1 Sample
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Vendor Name[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]Formula column[/TD]
[/TR]
[TR]
[TD]Vendor1[/TD]
[TD]Title1[/TD]
[TD]*need a formula Here[/TD]
[/TR]
[TR]
[TD]Vendor2[/TD]
[TD]Title2[/TD]
[TD]*need a formula Here[/TD]
[/TR]
</tbody>[/TABLE]






Sheet2 Sample
[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD]Title Category[/TD]
[TD]Vendor Name[/TD]
[TD]Possible Title1[/TD]
[TD]Possible Title 2[/TD]
[TD]Possible Title 3[/TD]
[/TR]
[TR]
[TD]Category1[/TD]
[TD]Vendor1[/TD]
[TD]Title1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category2[/TD]
[TD]Vendor2[/TD]
[TD]Title2[/TD]
[TD]Title4[/TD]
[TD]Title5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








Any help is appreciated. Never done anything like this so I am kinda lost.

 

[jasonb75]

With your sheet 2 sample in A1:E3 and your sheet 1 sample in H1:J3, try this formula in J2
=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$3)/($B$2:$B$3=H2)/($C$2:$E$3=I2),1))

 

[Johnny Thunder]

Is it possible to rewrite the formula you presented but tag the sheets to each Range? I am not sure where to link the values in your formula to each corresponding sheet.

I do appreciate the first pass at this though, so thank you!

 

[jasonb75]

Sure,
=INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$3)/(Sheet2!$B$2:$B$3=Sheet1!A2)/(Sheet2!$C$2:$E$3=Sheet1!B2),1))

Or a descriptive version, which might be easier to follow

=INDEX(Results-Entire-Column,AGGREGATE(15,6,ROW(1st Criteria Range)/(1st Criteria Range = 1st Criteria)/(2nd Criteria Range = 2nd Criteria),1))

Records in the data set that don't meet the criteria are eliminated by #DIV/0 errors. You can add more criteria if needed using the same method of /(Range = Criteria)

**Having read the information in post 1 again I'm starting to think that you might have merged cells, if you have then there is potential for the formula to fail **

 

[Johnny Thunder]

Awesome! That worked like a charm.... I still need to really understand how this Aggregate formula syntax works. Someone linked me to a write up but its like reading greek to me.

 

[jasonb75]

If you understand how =SUMPRODUCT((range1 = criteria1)*(range2 = criteria2)*(range3 = criteria3)) works then you are halfway there. The aggregate arrays work in the same way but using division instead of multiplication.

The purpose of this is to create #DIV/0 errors for any rows / columns that don't meet the given criteria.

The 2 numbers at the start of AGGREGATE 15,6 are selected options, 15 is the option for SMALL, 6 is the option to ignore errors and only look at the valid results in the array.

 

[Johnny Thunder]

I do use the Sumproduct method all the time so that reference really helped out. So now that I am half way there, with the 15=Small, what exactly does Small do/mean as opposed to Large?

 

[jasonb75]

Small looks at a list of numbers and evaluates them in the reverse order to Large. As the name suggests, the numbers are ranked from smallest to largest.

Looking at the formula that I provided for you, it is taking ROW(B2:B3) and dividing it by true or false. In theory, I think that you should only ever have 1 row that meets the specified criteria anyway, but if there are multiple rows that match then this would return the first one, the same as VLOOKUP with a FALSE (exact) match.

If you want to use Large with Aggregate, all you need to do is change 15 to 14.