 # Can you use AND / OR in an INDEX MATCH

Occasional Contributor

# Can you use AND / OR in an INDEX MATCH

Hi I have am array formula that looks like this:

=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works.

I want to add in an OR function for the name in column A. I will add this name in Column T. In other words the match is correct if column A or T match A2 and COL B=B2 and COL C=C2 also match the criteria

I tried using the + to add T criteria but gave me a 0

=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2)+('Rebate report'!A:A=T2),0),1)

Thanks for the help!

27 Replies

# Re: Can you use AND / OR in an INDEX MATCH

Hi David,

If it returns 0 that's something in your figures. Formula works like

if ABC or T matches it return some number

if ABC and T matches it returns #N/A

if no one of ABC and T matches it returns #N/A

# Re: Can you use AND / OR in an INDEX MATCH

Thanks Sergei

Your second statement is not what I am trying to do.

I want it to give a name in Column from the  Rebate Report if:

if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name

if A=A2 AND T=A2 AND B=B2 AND C=C2 return a cell ref for name. This should return a ref and not NA. This seemed different from what you said it would do in the formula.

If A not match A2 AND T also not match A2 OR B not match B2 OR C not match C2 then return NA.

Let me know if this makese sense. Thanks.

# Re: Can you use AND / OR in an INDEX MATCH

Hi David,

Just to clarify first one, where is OR?

A=A2 OR (t=A2 AND B = B2 AND C=C2)

(A=A2 OR t=A2) AND (B = B2 AND C=C2),

...

@David wrote:

if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name

# Re: Can you use AND / OR in an INDEX MATCH

Hi Sergei,

The second one is where the OR should be. Thanks

(A=A2 OR t=A2) AND (B = B2 AND C=C2),

# Re: Can you use AND / OR in an INDEX MATCH

Hi David,

More exactly

`(A=A2 OR A=T2) AND (B = B2 AND C=C2)`

, that was my misprint. In this case array formula could be

`=IFERROR(INDEX('Rebate report'!A:A,MATCH(1,((('Rebate report'!A:A=A2)+('Rebate report'!A:A=T2))>0)*('Rebate report'!B:B=B2)*('R'!C:C=C2),0),1),0)`

First multiplier in MATCH imitates OR condition, next are with AND

Thanks Sergei!

# Re: Can you use AND / OR in an INDEX MATCH

@Sergei Baklan  I read thiis old example and it seems to have worked. But, all I needed was a guide to use just OR in MATCHes (the addition of ANDs in the example got me confused on the brackets and 1/zeros   I need a way for a user to enter a dashboard cell with any of 3 simple texts - and for whatever they enter be MATCHed against 3 columns (B:B engine codes, or C:C job codes, or D:D part number) - if there's ANY mach - it is passed to INDEX to find the generic Account ID from A:A column. Many Thanks for any simple guidance JW

# Re: Can you use AND / OR in an INDEX MATCH

Assuming values in these 3 columns are always different (i.e. engine code never could be equal job code, etc.) variant of the formula is ``=INDEX(E3:E14,MATCH(1,INDEX( (B3:B14=H3)+(C3:C14=H3)+(D3:D14=H3),0),0))``

# Re: Can you use AND / OR in an INDEX MATCH

Oh my, Sergei, that's a winning solution!
I had researched having an Index within the Index but failed my attempts.
In addition, I simply did not realise each MATCH event would need to "=H3" so that each event is resolved (y/n, 0/1) Now I see it, the logic is obvious.
my sincere thanks. bolshoy spasiba Sergei

# Re: Can you use AND / OR in an INDEX MATCH

@Johnny_Warks27 , you are welcome, ne za chto

# Re: Can you use AND / OR in an INDEX MATCH

@Sergei Baklan Hi !!! I have the next sheets and the other one I need a formula that gives me the value of the cell C3 from sheet 2 if b3 from sheet1 = a3 from sheet2 and c3 sheet1 = b3 sheet 2 and e3 sheet 1 =d3 sheet2

# Re: Can you use AND / OR in an INDEX MATCH

You may transform your wording into IF() formula as

``````the cell C3 from sheet 2
if b3 from sheet1 = a3 from sheet2 and c3 sheet1 = b3 sheet 2 and e3 sheet 1 =d3 sheet2

=>

=IF( (B3=Sheet2!A3)*(C3=Sheet2!B3)*(E3=Sheet2!D3),
Sheet2!C3,
"no result"
)
``````

# Re: Can you use AND / OR in an INDEX MATCH

@Sergei Baklan Thanks a lot!!! The thing is I have two sheets with a table in each, and what I need is that excel returns the value of cells of column C from sheet 2, provided the above conditions are met.

ie  looks for the 3 matches of table 1 in table 2 and if it finds them, it shows the corresponding value of column C from table 2 in g3 in table 1, and apply all this to all the records in table 1

Thank you gain for your help

# Re: Can you use AND / OR in an INDEX MATCH

@RODRI1611 , not sure I understood entire logic, better with sample file. Anyway, will be glad if I was able to help.

# Re: Can you use AND / OR in an INDEX MATCH

Spoiler

@Sergei Baklan OK!!! There you are!!!

Sheet 1 Sheet 2 I want Excel to return in column P of sheet 1, the value of corresponding register from column B of sheet 2, provided that excel finds Sheet1!B2 in Sheet2!column A, and Sheet1!N2 in Sheet2!column C, and Sheet1!W2 in Sheet2!column E

Thanks again

# Re: Can you use AND / OR in an INDEX MATCH

Okay, thank you. Could you please clarify if XLOOKUP() is available for your version of Excel or not.

I´m afraid not
Just:
Buscar
BuscarV
BuscarH

# Re: Can you use AND / OR in an INDEX MATCH

Okay, when INDEX/MATCH/INDEX could work like

``=IFNA(INDEX(Sheet2!\$B\$2:\$B\$16, MATCH(1, INDEX((B2=Sheet2!\$A\$2:\$A\$16)*(N2=Sheet2!\$C\$2:\$C\$16)*(W2=Sheet2!\$E\$2:\$E\$16),0),0)),"no such")``

as in attached sample. If you open the file formula will be converted for your locale. Please adjust actual ranges. You may use something like Sheet2!A:A, etc instead of Sheet2!\$A2\$A16, but that affects performance. Or use dynamic range, even better to use Excel Tables.

# Re: Can you use AND / OR in an INDEX MATCH

@Sergei Baklan It worked properly!!!

Thanks soooo much!!!