• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic VLOOKUP?

D

Dustin Hoffman

Member
Here is the formula I am using:
=IF(A2="","",VLOOKUP(A2,'Rider Codes'!$C$2:$D$106,2,FALSE)

I have a VLOOKUP built that I am hoping I can adjust to potentially capture multiple possibilities within one cell. The formula above correctly converts column C(Full Name) to column D(Rider Name). The challenge is I have a handful of possibilities where two might be listed and shown like this:

GMIB (FIP99), TXPR (TAXP1)

Is it possible to convert more than one automatically and make the above example look something like this:

Family Income Protector, Taxpayer Rider

Here is part of the chart:
upload_2017-2-27_14-55-44.png
 
If a maximum of two codes could be in one cell, and the encoding is consistently 12 characters long, the following would find both and display both results in a single cell:

=IF(len(A2)=12,VLOOKUP(A2,'Rider Codes'!$C$2:$D$106,2,FALSE),VLOOKUP(left(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)&" and "&VLOOKUP(right(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE))
 
Mike86 said:
If a maximum of two codes could be in one cell, and the encoding is consistently 12 characters long, the following would find both and display both results in a single cell:

=IF(len(A2)=12,VLOOKUP(A2,'Rider Codes'!$C$2:$D$106,2,FALSE),VLOOKUP(left(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)&" and "&VLOOKUP(right(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE))
Click to expand...
 
Mike86,

Is there a way to update the formula to leave the cell empty if the reference cell is blank? Currently the empty ones show #N/A.
 
I've tried to use the IFERROR but am not doing it correctly.

=IFERROR(VLOOKUP(A2,'Rider Codes'!$C$2:$D$106,2,FALSE),VLOOKUP(LEFT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)&" and "&VLOOKUP(RIGHT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE))
 
Try This


=IFERROR(IF(VLOOKUP(A2,'Rider Codes'!$C$2:$D$106,2,FALSE),VLOOKUP(LEFT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)&" and "&VLOOKUP(RIGHT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)),"")

The IFERROR wraps ALL of your original formula - it does not replace the original IF statement. The "" at the end merely inserts a blank when an error is encountered.
 
Thanks David,

I modified it a little but I got it to work:

=IFERROR(IF(LEN(A2)=12,VLOOKUP(A2,'Rider Codes'!$C$2:$D$106,2,FALSE),VLOOKUP(LEFT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)&" and "&VLOOKUP(RIGHT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)),"")
 
Dustin Hoffman said:
Thanks David,

I modified it a little but I got it to work:

=IFERROR(IF(LEN(A2)=12,VLOOKUP(A2,'Rider Codes'!$C$2:$D$106,2,FALSE),VLOOKUP(LEFT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)&" and "&VLOOKUP(RIGHT(A2,12),'Rider Codes'!$C$2:$D$106,2,FALSE)),"")
Click to expand...


Great stuff - don't be afraid to try different approaches - in Excel there are usually several ways of getting to the solution you seek!
 
You must log in or register to reply here.
Back
Top