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

Copper Contributor

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)))

2 Replies

@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.

@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.