Forum Discussion
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
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
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))Hi Sergei, What does the INDEX function inside the MATCH function signify and How can we use INDEX inside MATCH or INDEX function?
That's the trick for the Excel which doesn't support dynamic arrays, e.g. Excel 2016. If in such Excel we use formula like
=INDEX(A:A, MATCH( 1, (B:B=2)*(C:C=3), 0 ) )here (B:B=2)*(C:C=3) is an array and we shall use above formula as an array one, i.e. to enter with Ctrl+Shift+Enter.
However, with INDEX( (B:B=2)*(C:C=3), 0 ) which returns exactly the same array we may use formula
=INDEX(A:A, MATCH( 1, INDEX( (B:B=2)*(C:C=3), 0 ), 0 ) )as regular one, i.e. enter it simply with Enter.
Above is not required for the Excel which supports dynamic arrays (Excel 365).
And on practice please don't use entire columns as in above sample, that significantly affects the performance. Structured tables, dynamic ranges or fixed ranges instead.
Hi Sergei,
I'm trying to do something similar but running into an issue where an incorrect value is returned when I drag down the formula to other item numbers,
I need to find column C where column A=A2 and column B = SPY or SHO
Item Org LTCategory 1081 ECH Stock 1081 NOF Stock 1081 AYR Stock 1081 RAN Stock 1081 JAX Stock 1081 PLC Stock 1081 SIG Stock - 6 PLT max 1081 GAL Stock - 6 PLT max 1081 WAS Stock - 6 UNT max 1081 SWE Stock - 6 UNT max 1081 BAL Stock - 6 UNT max 1081 SHO B 1081 SPY B 1081 BRI B - 6 UNT Max 1081 ALI D - 6 UNT max 21238 BRI B 21238 SHO D - 10 MSF min 21238 SWE F 21238 NOF F 21238 RAN F - 50 MSF min 21238 SIG F - 50 MSF min 21238 PLC F - 50 MSF min Hi SergeiBaklan
I am looking for something very similar to the formula you created for Johnny but I'm having no luck googling a solution. You mentioned the formula only works is the values in each column are never the same. But is there any way to modify the formulas so that it would work if some values were the same?
The reason I ask is that I have a workbook that compiles client data from across 5 different databases. A mini master data table. Each row = 1 client. And for each client in the table there's 5 client name columns (1 name from each database). Sometimes they're the same but often there's discrepancies between the 5 DBs.
So I'm hoping to write a formula that says if you match "Freds Windows" in any of the name columns say C:G then return the account reference number in column A:A
Is something like that possible?
All the best
Vicky
Here is practically the same idea as JMB17 suggested but for Excel 365, plus I understood the logic of source data in a bit different way
with
=LET(Clients, $A$1:$A$6, Range, $C$1:$G$6, name, $C$8, IFERROR( INDEX( FILTER(Clients, MMULT(COUNTIFS(name,Range), SEQUENCE(COLUMNS(Range),,,0) ) ), 1), "name wasn't found"))
