Using Index Match function with IF based on certain criteria

[Dellboy]

Good afternoon excel guru's,

I have a formula that currently works which looks returns a value based on another cell, as follows;

=INDEX(A:A,MATCH(B2, C:C,0))

My question is how do I add the IF function if I want it to search for values based on certain criteria, e.g. only return values from column A if Column D has "Yes" and Column E has "Apple".

 

[Fluff]

How about

=INDEX($A$2:$A$1000,AGGREGATE(15,6,(ROW($A$2:$A$1000)-ROW($A$2)+1)/($C$2:$C$1000=B2)/($D$2:$D$1000="Yes")/($E$2:$E$1000="Apple"),1))

 

[JoeMo]

Another way:

Book1
	A	B	C	D	E
1	Data1	Data14	XY16	Yes	Apple
2	Data2	XY7	XY6	No	Pear
3	Data3		XY16	No	Plum
4	Data4		XY5	No	Apple
5	Data5		XY8	Yes	Apple
6	Data6		XY6	No	Plum
7	Data7		XY9	Yes	Apple
8	Data8		XY5	No	Pear
9	Data9		XY11	Yes	Plum
10	Data10		XY1	No	Apple
11	Data11		XY19	Yes	Pear
12	Data12		XY16	No	Plum
13	Data13		XY7	Yes	Pear
14	Data14		XY7	Yes	Apple
15	Data15		XY3	Yes	Plum
16	Data16		XY18	No	Apple
17	Data17		XY1	Yes	Pear
18	Data18		XY18	No	Plum
19	Data19		XY7	Yes	Apple
20	Data20		XY10	No	Pear
Sheet2

Cell Formulas
Range	Formula
B1	B1	=INDEX($A$1:$A$20,MATCH(1, ($C$1:$C$20=B2)*($D$1:$D$20="Yes")*($E$1:$E$20="Apple"),0))

 

[AhoyNC]

Another option using the FILTER function in 365 instead of INDEX.

Book1
	A	B	C	D	E	F	G
1	Data1		XY16	Yes	Apple		Data14
2	Data2	XY7	XY6	No	Pear		Data19
3	Data3		XY16	No	Plum
4	Data4		XY5	No	Apple
5	Data5		XY8	Yes	Apple
6	Data6		XY6	No	Plum
7	Data7		XY9	Yes	Apple
8	Data8		XY5	No	Pear
9	Data9		XY11	Yes	Plum
10	Data10		XY1	No	Apple
11	Data11		XY19	Yes	Pear
12	Data12		XY16	No	Plum
13	Data13		XY7	Yes	Pear
14	Data14		XY7	Yes	Apple
15	Data15		XY3	Yes	Plum
16	Data16		XY18	No	Apple
17	Data17		XY1	Yes	Pear
18	Data18		XY18	No	Plum
19	Data19		XY7	Yes	Apple
20	Data20		XY10	No	Pear
Sheet1

Cell Formulas
Range	Formula
G1	G1	=FILTER($A$1:$A$20,($C$1:$C$20=$B$2)*($D$1:$D$20="Yes")*($E$1:$E$20="Apple"))

 

[Dellboy]

hmmm ,struggling with this one. Think there might be an easier way.

Basically, I want to populate a cell with a value from the same line of another value. Take for example the table below;

A1 -2020	A2 -Yes	A3 - Apple
B1 - 2020	B2 -Yes	B3 - Apple
C1- 2021	C2 - No	C3 - Pear
D1 - 2022	D2- Yes	D3 - Banana
E1 - 2020	E2 - No	E3 - Orange
H1 = D1 = (2022)	H2 = match D1 line with D2 = (Yes)	H3 = match D1 line wtih D3 = (Banana)

So, I want the cells marked test to return the values 'Yes' and 'Banana' based on the values from 2022. I think it would be either match or vlookup function? so if 2022 is populated, it looks up the value from D2 (for H2) and D3 (for H3).

Hopefully that makes sense, just want to automatically lookup the value from a cell on the same line.

 

[AhoyNC]

What answer do you want if there is more than one match as for 2020?

 

[Dellboy]

Is there not a way to look it up from the same line (e.g. column D) then take the values there. Or do I need to include the search criteria to include "yes" and "banana" to get the exact match?

 

[AhoyNC]

If you put 2022 in cell H1 there is only one match. However if you put 2020 in cell H1 there are 3 columns that match.

See example below, one using XLOOKUP and the other using FILTER.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	2020	2020	2021	2022	2020			2022		2020
2	Yes	Yes	No	Yes	No			Yes		Yes	Yes	No
3	Apple	Apple	Pear	Banana	Orange			Banana		Apple	Apple	Orange
Sheet1

Cell Formulas
Range	Formula
H2	H2	=XLOOKUP($H$1,$A$1:$E$1,$A$2:$E$3,"")
J2	J2	=FILTER($A$2:$E$3,$A$1:$E$1=$J$1)