LOOKUP Multiple criteria

[Pat Mor]

Hello
I have data in A5 - E16 and need a result in H2 and H3 based on criteria, see below example
Cell H2 : result = V1
Criteria 1 : 100 in column B
Criteria 2 : Z in column A
Lookup which data in Row 8 (cells C8 - D8 - E8) matches in column A

Is this possible without an array formula ?

Regards

 

[Alex Blakenburg]

What criteria are you using for H3 to return V7 instead of V5 ?
Here is an option without the column selection part

Book4
	A	B	C	D	E	F	G	H	I
1							Criteria	Z	<-- Criteria
2							100	V1
3							200	V5
4
5			Info 1	Info 2	Info 3
6	P1	100
7	P2	100
8	Z	100	V1	V2	V3
9	P3	200
10	Z	200	V5	V7	V10
11	P4	300
12	V1	300
13	V4	300
14	P4	300
15	V6	300
16	V7	300
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=LOOKUP(2, 1/ (($B$6:$B$16=G2) * ($A$6:$A$16=$H$1)), $C$6:$C$16)

 

[Pat Mor]

Hi Alex
V5 is not in column A
V7 is in column A - cell A16

Regards

 

[Alex Blakenburg]

The requirement statement was to look for Z in column A.
Z is on rows 8 and 10.
So if you want V7 with a column A requirement of "Z" and a column B requirement of 200, it has to look in Info 2.
There is no criteria to tell it to do so.

 

[Pat Mor]

Hi Alex

Correct there is no criteria to look in which info column.
I was thinking of first looking for column C, if no match, look for column D, if still no match, look for column E
e.g. =+IFNA(VLOOKUP(C8;A:A;1;FALSE);IFNA(VLOOKUP(D8;A:A;1;FALSE);VLOOKUP(E8;A:A;1;FALSE)))

But how do I combine this in one cell with the other criteria in H1 and G2 ?

Regards

 

[Alex Blakenburg]

If you don't have MS365 or Excel 2021 then the formula is going to get quite long.
A not found (NA) is quite different to it having a blank in Info 1 and/or info 2

20230103 MultiCriteria Lookup Pat Mor.xlsx
	A	B	C	D	E	F	G	H	I
1							Criteria	Z	<-- Criteria
2							100	V1
3							200	V7
4
5			Info 1	Info 2	Info 3
6	P1	100
7	P2	100
8	Z	100	V1	V2	V3
9	P3	200
10	Z	200		V7	V10
11	P4	300
12	V1	300
13	V4	300
14	P4	300
15	V6	300
16	V7	300
Sheet1 V02

Cell Formulas
Range	Formula
H2:H3	H2	=IF(LOOKUP(2,1/(($B$6:$B$16=G2)*($A$6:$A$16=$H$1)),$C$6:$C$16)<>"",LOOKUP(2,1/(($B$6:$B$16=G2)*($A$6:$A$16=$H$1)),$C$6:$C$16), IF(LOOKUP(2,1/(($B$6:$B$16=G2)*($A$6:$A$16=$H$1)),$D$6:$D$16)<>"",LOOKUP(2,1/(($B$6:$B$16=G2)*($A$6:$A$16=$H$1)),$D$6:$D$16), IF(LOOKUP(2,1/(($B$6:$B$16=G2)*($A$6:$A$16=$H$1)),$E$6:$E$16)<>"",LOOKUP(2,1/(($B$6:$B$16=G2)*($A$6:$A$16=$H$1)),$E$6:$E$16), "")))