Forum Discussion

David's avatar
David
Copper Contributor
Oct 27, 2017

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...
Excel Desktop
Formulas & Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 27, 2020

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

=INDEX(E3:E14,MATCH(1,INDEX( (B3:B14=H3)+(C3:C14=H3)+(D3:D14=H3),0),0))
Vicky1234's avatar
Vicky1234
Copper Contributor
Mar 23, 2021

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

  • j_kistner2441's avatar
    j_kistner2441
    Copper Contributor
    Jun 05, 2021

    SergeiBaklan 

    I'm trying to create a formula that will bring back the price for a specific customer and item combination. The part I'm having issues with is with the quantity sold. Some items have a singular price (meaning if the customer buys 1 piece or 1,000 pieces, they are going to pay the same price), while other items have price breaks depending on the quantity they buy. For example, if the customer buys 1-500 pieces of item 123, they'll pay $1. If they buy 501-1,000 pieces of item 123, they will pay $0.50.  Any advise would be appreciated!

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 23, 2021

    Vicky1234 

    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"))
  • JMB17's avatar
    JMB17
    Bronze Contributor
    Mar 23, 2021

    Vicky1234 

     

    If I may offer a suggestion, I believe what you want to do will be the same except you just need to test if the sum of the OR conditions is> 0. So,

     

    ((C:C="Fred Windows)+(D:D="Fred Windows)+(E:E="Fred Windows)+(F:F="Fred Windows)+(G:G="Fred Windows)>0)

     

    Of course, the above conditional test would then be multiplied by any AND conditions you have.

     

    See attached for some examples. Note that if you don't have office 365 with dynamic arrays, then you will need to hit Ctrl+Shift+Enter after keying the formula instead of just enter.

Resources