Vlookup formula

[floggingmolly]

I have the formula below which works. It looks in column 11 and if blank it uses the value in column 12. If the value in H2 isn't in my table array, how can I hide the #N/A error? Any help would be greatly appreciated.

=IF(VLOOKUP(H2,BDS_Alignment,11,FALSE)="",VLOOKUP(H2,BDS_Alignment,12,FALSE),VLOOKUP(H2,BDS_Alignment,11,FALSE))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dhinakaran]

You can use the IFERROR function to catch the error and return any value you like as below.
=IF(VLOOKUP(H2,BDS_Alignment,11,FALSE)="",VLOOKUP(H2,BDS_Alignment,12,FALSE),VLOOKUP(H2,BDS_Alignment,11,FALSE)),"Not Found")

I have the formula below which works. It looks in column 11 and if blank it uses the value in column 12. If the value in H2 isn't in my table array, how can I hide the #N/A error? Any help would be greatly appreciated.

=IF(VLOOKUP(H2,BDS_Alignment,11,FALSE)="",VLOOKUP(H2,BDS_Alignment,12,FALSE),VLOOKUP(H2,BDS_Alignment,11,FALSE))

 

[floggingmolly]

You can use the IFERROR function to catch the error and return any value you like as below.
=IF(VLOOKUP(H2,BDS_Alignment,11,FALSE)="",VLOOKUP(H2,BDS_Alignment,12,FALSE),VLOOKUP(H2,BDS_Alignment,11,FALSE)),"Not Found")

I tried this but it says There's a problem with this formula.

 

[Joe4]

Did you see Fluff's question?
There are some cool new options in the newer versions of Excel, but we need to know what version of Excel you are running, to determine if they might work for you.
So I recommend you update your account details, like he asked.

 

[Fluff]

So I recommend you update your account details, like he asked.

I've asked the OP the same question in various threads & never got an answer.

 

[floggingmolly]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am using 365. Version 2108.

 

[Joe4]

I am using 365. Version 2108.

Can you update your Profile, as instructed in the conversation you just quoted?
This will make it appear in your Profile, so it shows up on ALL of your posts, and will aid helpers with future questions that you ask.

Thanks.

 

[Fluff]

Thanks for that, how about

=LET(v,VLOOKUP(H2,BDS_Alignment,11,FALSE), IF(ISNA(v),"",IF(v<>"",v,IFNA(VLOOKUP(H2,BDS_Alignment,11,FALSE),""))))

Please don't forget to update your profile.

 

[floggingmolly]

Thanks for that, how about

=LET(v,VLOOKUP(H2,BDS_Alignment,11,FALSE), IF(ISNA(v),"",IF(v<>"",v,IFNA(VLOOKUP(H2,BDS_Alignment,11,FALSE),""))))

Please don't forget to update your profile.

I tried this and doesn't work. It returns all 0's.