Can anyone tell me why this formula returns a #VALUE error? Each part of the formula returns the correct value when I select it and press F9, so I'm assuming its a simple syntax error that I can't see!
=INDEX(ClientTable[Company],MATCH([@[First Name]]&[@[Last Name]],ClientTable[First Name]&ClientTable[Last Name]),0)
thanks
Hello,
As this support page mentions, you probably need to enter the formula as an array formula. When checking this article for more info it seems very likely to be the case.
”If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula.”
Many thanks Anders, I thought I’d seen somewhere that INDEX is clever enough to know when its an array and therefore Ctrl+shift+enter not needed.
Seems that may not be the case so I’ll look into your suggestion. Thanks again
I've relooked at this and entered the formula as an array formula and I do get a resolved answer but its the wrong value from 620 rows further down the lookup array than the correct value - I must be missing something obvious.
The formula that returns a valid, but incorrect result is
={INDEX(ClientTable[Company],MATCH([@[First Name]]&[@[Last Name]],ClientTable[First Name]&ClientTable[Last Name]),0)}
Hello,
In this article you have a similar lookup scenario as yours, have you tried this?
If it is not working then it would be great if you can upload a sample file for us to play with.
I think you have a bracket in the wrong place, and it should be:
=INDEX(ClientTable[Company],MATCH([@[First Name]]&[@[Last Name]],ClientTable[First Name]&ClientTable[Last Name],0))
still array-entered with Ctrl+Shift+Enter. Or, if there should only be one match, use:
=LOOKUP(2,1/(ClientTable[First Name]=[@[First Name]])/(ClientTable[Last Name]=[@[Last Name]]),ClientTable[Company])
normally entered.
