Forum Discussion

123Richmont69's avatar
123Richmont69
Copper Contributor
Dec 02, 2020

Having a problem with Index Match formula. Keeps giving me a value error

Hello Community  -  I have an urgent business meeting and need to get this error fixed.  

 

My formula works perfectly if I just use two index match statements.   When I add a third, it gives me a Value error.    There is no issue with the data type....even though that is the error I get.  

 

The formula works fine if I just have the first two index/match statements.   As soon as I add a third, I start getting the error. 

 

=IF(K655="PSU Rev A",INDEX(Rev_A[Qty To Return],MATCH([@[Ship To Customer]],Rev_A[Ship To Customer],0)),IF(K655="PSU Rev B",INDEX(Rev_B[Qty to Return],MATCH([@[Ship To Customer]],Rev_B[Ship To Customer],0)))),IF(K655="PSE1",INDEX(PSE_1[Qty to Return],MATCH([@[Ship To Customer]],PSE_1[Ship To Customer],0)))

excel
Formulas and Functions

2 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Dec 02, 2020

    123Richmont69 

    I recommend this formula: 

    =CHOOSE(MATCH(K655,{"PSU Rev A","PSU Rev B","PSE1"},0),
INDEX(Rev_A[Qty To Return],
MATCH([@[Ship To Customer]],Rev_A[Ship To Customer],0)),
INDEX(Rev_B[Qty to Return],
MATCH([@[Ship To Customer]],Rev_B[Ship To Customer],0)),
INDEX(PSE_1[Qty to Return],
MATCH([@[Ship To Customer]],PSE_1[Ship To Customer],0)))

     The foregoing formula is easier to understand and faster to calculate.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Dec 02, 2020

    123Richmont69 

     

    I suspect that your problem lies in the use of IF rather than the INDEX/MATCH area. Have you tried the IFS function? It can often work more readily (and, not incidentally, be more readable) that having deeply nested IF functions.

     

    Here's a helpful reference: https://exceljet.net/excel-functions/excel-ifs-function

     

    If you still need help, may I suggest that you post a copy (or a representative sample) of your actual spreadsheet. Not an image, the actual spreadsheet, with rows and columns of representative data.

Resources