Oct 27 2017
- last edited on
Jul 25 2018
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!
Oct 27 2017 11:04 AM
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
Oct 28 2017 09:51 AM
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.
Oct 31 2017 03:16 PM
(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
Oct 27 2020 04:46 AM
@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
Oct 29 2020 07:02 AM
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
Jan 05 2021 01:12 AM
@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
Thank you very muchin advance
Jan 05 2021 01:31 AM
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" )
Jan 05 2021 04:02 AM
@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
Jan 05 2021 04:45 AM
@Sergei Baklan OK!!! There you are!!!
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
Jan 05 2021 05:23 AM
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.