New Contributor

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

2 Replies

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

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.

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.

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

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.