Oct 27 2017
10:05 AM
- last edited on
Jul 25 2018
10:17 AM
by
TechCommunityAP
Oct 27 2017
10:05 AM
- last edited on
Jul 25 2018
10:17 AM
by
TechCommunityAP
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
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
Oct 28 2017 09:51 AM
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.
Oct 28 2017 10:06 AM
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
Oct 31 2017 01:01 PM
Hi Sergei,
The second one is where the OR should be. Thanks
(A=A2 OR t=A2) AND (B = B2 AND C=C2),
Oct 31 2017 03:16 PM
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
Oct 27 2020 04:46 AM
@SergeiBaklan 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
Oct 27 2020 01:31 PM
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))
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
Oct 29 2020 01:08 PM
@Johnny_Warks27 , you are welcome, ne za chto
Jan 05 2021 01:12 AM
@SergeiBaklan 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
@SergeiBaklan 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:19 AM
@RODRI1611 , not sure I understood entire logic, better with sample file. Anyway, will be glad if I was able to help.
Jan 05 2021 04:45 AM
@SergeiBaklan 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
Jan 05 2021 04:55 AM
Okay, thank you. Could you please clarify if XLOOKUP() is available for your version of Excel or not.
Jan 05 2021 05:02 AM
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.
Jan 14 2021 12:48 AM
@SergeiBaklan It worked properly!!!
Thanks soooo much!!!