Forum Discussion
Vlookup and return first value in list that is NOT NULL
Hi everyone,
I have an issue where my VLOOKUP alone does not work as it will always return the first entry in my table. In my case I have several entries for Account name and it picks up the first entry which has null - I want it to pick up the first entry which is not null.
The data as it stands does not lend itself to VLOOKUP, but I am unsure which formula to use. I have excel for MAC v16.37
Please find attached my example - the cell highlighted in yellow should say Active, as there is an Active associated with the account name that year. If there was no Active value at all, it is correct to say Null.
Thanks in advance for your help!
As variant in U11 it could be
=IFERROR( INDEX($F$2:$I$2351, MATCH(1,INDEX(($A$2:$A$2351=$T11)*(INDEX($F$2:$I$2351,,MATCH(U$10,$F$1:$I$1,0))<>""),0),0), MATCH(U$10,$F$1:$I$1,0)), "")and drag it to the right
5 Replies
Converted to a table
= IFERROR(
LOOKUP( 2,
1 / (Table1[EC Account: Account Name]=acName) / (Table1[2020]="ACTIVE"),
Table1[2020] ),
"NULL" )
As variant in U11 it could be
=IFERROR( INDEX($F$2:$I$2351, MATCH(1,INDEX(($A$2:$A$2351=$T11)*(INDEX($F$2:$I$2351,,MATCH(U$10,$F$1:$I$1,0))<>""),0),0), MATCH(U$10,$F$1:$I$1,0)), "")and drag it to the right
- Thank you x 1 million! Super helpful!
JennyHoA20181 , you are welcome
JennyHoA20181 try attached file.
I have used intermediate steps, via 'Dummy' columns under column AA to AE
I have also updated formulas for U11:X11 (highlighted them in Yellow)
