Using Index Match function with IF based on certain criteria

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365
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".
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,012
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Another way:
Book1
ABCDE
1Data1Data14XY16YesApple
2Data2XY7XY6NoPear
3Data3XY16NoPlum
4Data4XY5NoApple
5Data5XY8YesApple
6Data6XY6NoPlum
7Data7XY9YesApple
8Data8XY5NoPear
9Data9XY11YesPlum
10Data10XY1NoApple
11Data11XY19YesPear
12Data12XY16NoPlum
13Data13XY7YesPear
14Data14XY7YesApple
15Data15XY3YesPlum
16Data16XY18NoApple
17Data17XY1YesPear
18Data18XY18NoPlum
19Data19XY7YesApple
20Data20XY10NoPear
Sheet2
Cell Formulas
RangeFormula
B1B1=INDEX($A$1:$A$20,MATCH(1, ($C$1:$C$20=B2)*($D$1:$D$20="Yes")*($E$1:$E$20="Apple"),0))
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Another option using the FILTER function in 365 instead of INDEX.

Book1
ABCDEFG
1Data1XY16YesAppleData14
2Data2XY7XY6NoPearData19
3Data3XY16NoPlum
4Data4XY5NoApple
5Data5XY8YesApple
6Data6XY6NoPlum
7Data7XY9YesApple
8Data8XY5NoPear
9Data9XY11YesPlum
10Data10XY1NoApple
11Data11XY19YesPear
12Data12XY16NoPlum
13Data13XY7YesPear
14Data14XY7YesApple
15Data15XY3YesPlum
16Data16XY18NoApple
17Data17XY1YesPear
18Data18XY18NoPlum
19Data19XY7YesApple
20Data20XY10NoPear
Sheet1
Cell Formulas
RangeFormula
G1G1=FILTER($A$1:$A$20,($C$1:$C$20=$B$2)*($D$1:$D$20="Yes")*($E$1:$E$20="Apple"))
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365

ADVERTISEMENT

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 -2020A2 -YesA3 - Apple
B1 - 2020B2 -YesB3 - Apple
C1- 2021C2 - NoC3 - Pear
D1 - 2022D2- YesD3 - Banana
E1 - 2020E2 - NoE3 - 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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
What answer do you want if there is more than one match as for 2020?
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKL
12020202020212022202020222020
2YesYesNoYesNoYesYesYesNo
3AppleApplePearBananaOrangeBananaAppleAppleOrange
Sheet1
Cell Formulas
RangeFormula
H2H2=XLOOKUP($H$1,$A$1:$E$1,$A$2:$E$3,"")
J2J2=FILTER($A$2:$E$3,$A$1:$E$1=$J$1)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top