
Dashboards

August 14, 2019

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

VIP

Trusted Members

December 7, 2016

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

Dashboards

August 14, 2019

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

VIP

Trusted Members

December 7, 2016

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.


Trusted Members
Moderators

November 1, 2018

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.
1 Guest(s)
