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

%3CLINGO-SUB%20id%3D%22lingo-sub-1948924%22%20slang%3D%22en-US%22%3EHaving%20a%20problem%20with%20Index%20Match%20formula.%20Keeps%20giving%20me%20a%20value%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948924%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community%26nbsp%3B%20-%26nbsp%3B%20I%20have%20an%20urgent%20business%20meeting%20and%20need%20to%20get%20this%20error%20fixed.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formula%20works%20perfectly%20if%20I%20just%20use%20two%20index%20match%20statements.%26nbsp%3B%20%26nbsp%3BWhen%20I%20add%20a%20third%2C%20it%20gives%20me%20a%20Value%20error.%26nbsp%3B%20%26nbsp%3B%20There%20is%20no%20issue%20with%20the%20data%20type....even%20though%20that%20is%20the%20error%20I%20get.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20works%20fine%20if%20I%20just%20have%20the%20first%20two%20index%2Fmatch%20statements.%26nbsp%3B%20%26nbsp%3BAs%20soon%20as%20I%20add%20a%20third%2C%20I%20start%20getting%20the%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(K655%3D%22PSU%20Rev%20A%22%2CINDEX(Rev_A%5BQty%20To%20Return%5D%2CMATCH(%5B%40%5BShip%20To%20Customer%5D%5D%2CRev_A%5BShip%20To%20Customer%5D%2C0))%2CIF(K655%3D%22PSU%20Rev%20B%22%2CINDEX(Rev_B%5BQty%20to%20Return%5D%2CMATCH(%5B%40%5BShip%20To%20Customer%5D%5D%2CRev_B%5BShip%20To%20Customer%5D%2C0))))%2CIF(K655%3D%22PSE1%22%2CINDEX(PSE_1%5BQty%20to%20Return%5D%2CMATCH(%5B%40%5BShip%20To%20Customer%5D%5D%2CPSE_1%5BShip%20To%20Customer%5D%2C0)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1948924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1949208%22%20slang%3D%22en-US%22%3ERe%3A%20Having%20a%20problem%20with%20Index%20Match%20formula.%20Keeps%20giving%20me%20a%20value%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1949208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F618003%22%20target%3D%22_blank%22%3E%40123Richmont69%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20that%20your%20problem%20lies%20in%20the%20use%20of%20IF%20rather%20than%20the%20INDEX%2FMATCH%20area.%20Have%20you%20tried%20the%20%3CSTRONG%3EIFS%3C%2FSTRONG%3E%20function%3F%20It%20can%20often%20work%20more%20readily%20(and%2C%20not%20incidentally%2C%20be%20more%20readable)%20that%20having%20deeply%20nested%20IF%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20helpful%20reference%3A%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-ifs-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-ifs-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20still%20need%20help%2C%20may%20I%20suggest%20that%20you%20post%20a%20copy%20(or%20a%20representative%20sample)%20of%20your%20actual%20spreadsheet.%20Not%20an%20image%2C%20the%20actual%20spreadsheet%2C%20with%20rows%20and%20columns%20of%20representative%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.