Can you use AND / OR in an INDEX MATCH

Copper Contributor

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!

59 Replies

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

 

 

 

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.

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

 

Hi Sergei,

 

The second one is where the OR should be. Thanks

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

 

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!

@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

@Johnny_Warks27 

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

image.png

=INDEX(E3:E14,MATCH(1,INDEX( (B3:B14=H3)+(C3:C14=H3)+(D3:D14=H3),0),0))

@Sergei Baklan 

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

@Johnny_Warks27 , you are welcome, ne za chto

@Sergei Baklan Hi !!! I have the next sheets

RODRI1611_0-1609837665354.png

and the other one

RODRI1611_1-1609837709310.png

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

 

Thank you very muchin advance

 

@RODRI1611 

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"
)

@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

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

Spoiler
 

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

 

Sheet 1

 

RODRI1611_0-1609850180195.png

 

Sheet 2 

 

RODRI1611_1-1609850248350.png

 

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

 

@RODRI1611 

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

@RODRI1611 

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.

@Sergei Baklan It worked properly!!!

Thanks soooo much!!!