Dec 02 2020 07:23 AM
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)))
Dec 02 2020 08:24 AM
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.
Dec 02 2020 10:12 AM
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.