IF function with Vlookup

[kmprice710]

I have a spreadsheet that looks like this:


[TABLE="class: grid, width: 512"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD]old_ID
[/TD]
[TD]new_ID
[/TD]
[TD][/TD]
[TD]badid[/TD]
[TD]old_ID[/TD]
[TD][/TD]
[TD]new_ID[/TD]
[TD]current_ID
[/TD]
[/TR]
[TR]
[TD]adams1
[/TD]
[TD="colspan: 2"]ma000262
[/TD]
[TD]gerger[/TD]
[TD]adams1[/TD]
[TD][/TD]
[TD][/TD]
[TD]gerger[/TD]
[/TR]
[TR]
[TD]akin1[/TD]
[TD]za000804[/TD]
[TD][/TD]
[TD]ynytn[/TD]
[TD]akin1[/TD]
[TD][/TD]
[TD][/TD]
[TD]ynytn[/TD]
[/TR]
[TR]
[TD]aly1[/TD]
[TD]ca001425[/TD]
[TD][/TD]
[TD]wdwwef
[/TD]
[TD]aly1[/TD]
[TD][/TD]
[TD][/TD]
[TD]wdwwef[/TD]
[/TR]
[TR]
[TD]amerau1[/TD]
[TD]ca001480[/TD]
[TD][/TD]
[TD]tryjyj[/TD]
[TD]amerau1[/TD]
[TD][/TD]
[TD][/TD]
[TD]adams1[/TD]
[/TR]
[TR]
[TD]ashburn1
[/TD]
[TD]ba002831
[/TD]
[TD][/TD]
[TD]yukuy[/TD]
[TD]ashburn1[/TD]
[TD][/TD]
[TD][/TD]
[TD]akin1[/TD]
[/TR]
[TR]
[TD]avery1
[/TD]
[TD]ha111680[/TD]
[TD][/TD]
[TD]verevv[/TD]
[TD]avery1[/TD]
[TD][/TD]
[TD][/TD]
[TD]aly1[/TD]
[/TR]
[TR]
[TD]balias1[/TD]
[TD]cb003836[/TD]
[TD][/TD]
[TD]jytjtyb[/TD]
[TD]balias1[/TD]
[TD][/TD]
[TD][/TD]
[TD]amerau1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ashburn1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]avery1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]balias1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yukuy
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]verevv
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


For the new_ID column, usually I'd do a Vlookup to match the Old_ID to the Current_ID to produce new_ID. For example:

=VLOOKUP(H2,$A$1:$B$8,2,FALSE)

but I already know that some of the current_Id need to be converted from Badid into Old_ID first and then converted to new_ID.

Is there a way that I can do this with Nested If statements and Vlookup?

 

[rniculae]

hello,

what about: =iferror(vlookup(VLOOKUP(H2,$d$1:$e$8,2,0),$A$1:$B$8,2,0),VLOOKUP(H2,$A$1:$B$8,2,0)) ?

 

[kmprice710]

hello,

what about: =iferror(vlookup(VLOOKUP(H2,$d$1:$e$8,2,0),$A$1:$B$8,2,0),VLOOKUP(H2,$A$1:$B$8,2,0)) ?

It seems to work just fine. I was using IFNA and was getting 'NAME?' back. Any idea why?

 

[rniculae]

No idea, probably something went wrong in your formula. try to use evaluate formula and see why you got a #name ?.

 

[kmprice710]

No idea, probably something went wrong in your formula. try to use evaluate formula and see why you got a #name ?.

One thing I noticed with my formula is that I was using false instead of 0. Why did you use 0?

Also, can you walk me through the logic of the If statement? I really appreciate your help. I like to figure out why it works, too.

 

[rniculae]

I'm, just used to put 0 for exact match on vlookup. You can use false also.

=iferror(vlookup(VLOOKUP(H2,$d$1:$e$8,2,0),$A$1:$B$8,2,0),VLOOKUP(H2,$A$1:$B$8,2,0))

let's drill down. VLOOKUP(H2,$d$1:$e$8,2,0) this will search current id in bad id and will result a old id. If not, you will have an error.
This vlookup(VLOOKUP(H2,$d$1:$e$8,2,0),$A$1:$B$8,2,0) will get the old id from previous vlookup and will search again like old id resulted from above, in old id and will result in new id.
Now you have 2 options, you will receive a new id or you will get a #n/a because one of the vlookups (most probably the first one) will get an error. That's why we have the iferror.
If there is an error, it will search for current id in old id and will get you new id.

I hope I could explain it to you.