Comparing lists and getting back a value in vlookup where there are sometimes blanks

[Katiemary]

Hi

I'm hoping you can help me with this problem. I'm comparing car registration numbers by driver from two different lists using vlookup and I need to see where they do and don't match. The lists sometimes contain blanks, and I've created a formulae to show where the Reg's match and don't (some have gaps inbetween the charachters some don't hence >=):

The vlookup function I've used is:

=IFERROR(VLOOKUP(A2,$J$2:$N$236,COLUMN(D1),0),"")

Then in a separate column, to differentiate the data:

=IF(E2>=G2,"Same Reg","Different Reg")

Unfortunately, where there are blanks, it comes back the same i.e. if one list has a blank, it comes back 'Different Reg' or where they both have a blank (no entries in either list for that driver), it comes back 'Same Reg'. I need to differentiate by saying either 'Single Entry' for one list being blank for that driver or 'No Entries' for both lists being blank for that driver.

I would like the one column to show either 'same reg', different reg', 'Single entry' or 'No Entries' and just don't know how to add more conditions to my formulae to do this.

I'm using excel 2007. Here's what I mean - the last column is what I'd like it to say - the one before is how it displays it currently:

<TABLE style="WIDTH: 620pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=825><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 117pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl86 height=17 width=156 colSpan=2>List 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 144pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl67 width=192 colSpan=2>List 2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fac090; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=86> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #92d050; WIDTH: 199pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl73 width=265 colSpan=3>Results Colums</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=126></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 63pt; HEIGHT: 25.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl88 height=34 width=84>EmployeeRef</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; WIDTH: 54pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89 width=72>LD Reg list</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fac090; WIDTH: 61pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 width=81>Employee ID</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fac090; WIDTH: 83pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71 width=111>TMC Reg Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fac090; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=86>Vehicle Make</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #92d050; WIDTH: 56pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76 width=75>Reg match</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #92d050; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl77 width=86>Vehicle Make</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #92d050; WIDTH: 78pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78 width=104>Match type</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=126>Would like it to say</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17 align=right>50000001</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90>DE60NXU</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>50000001</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl84>DE60NXU</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Peugeot</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79>DE60NXU</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl80>Peugeot</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>Same Reg</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Same Reg</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17 align=right>50000004</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>50000004</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl85> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Hyundai</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79>J55CCS</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl80>Hyundai</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82>Same Reg</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl83>No Entries</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17 align=right>50000007</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>50000007</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl85>DC04TJY</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>MAZDA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79>DC04TJY</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl80>MAZDA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>Different Reg</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">single entry</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17 align=right>50000025</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90>HN09HZB</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>50000026</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl85>T20 RBA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Alfa Romeo </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl79>HN09HZB</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl80>BMW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82>Different Reg</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Different Reg</TD></TR></TBODY></TABLE>

If any one can help - bearing in mnd I'm a beginner at all this so expalin as though to a year old - I'd be eternally grateful

 

[cstimart]

Sounds like you're looking for a Nested If statement. Typically, when I build them, I write out the ifs...and copy/paste them together.

For Example:

=If(A=B,"B",0)
=If(A=C,"C",0)
=If(A=D,"D",0)

then, replace the 0 with the next If..

=If(A=B,"B",If(A=C,"C",If(A=D,"D",0)))

 

[Katiemary]

Thanks , this is really helpful....I've made and tested my list:

=IF(B3>=F3,"Same Reg","Different Reg")
=IF(ISBLANK(D4),"single entry",0)

but can't figure out how to differntiate between where one cell is blank (single entry) and then if two cells are blank (No entries) and indeed so my first formulae just doesn't see two blanks as being the same and return .
'same reg'.
I tried

=IF(ISBLANK(D6&F6),"No entry",0)

and even though it didn't come back as an error, it just returned a 0.

Any ideas? I just tried putting an '&' and another cell ref as really just stumbling about these formulaes .

Sorry to be a pain and really appreciate the help.

 

[cstimart]

Try something like...

=IF(AND(D6="",F6=""),"No Entry",0)

 

[Katiemary]

Wow, that's excellent. The formulae I finally cobbled together was:

=IF(AND(E2="",M2=""),"No Entry",IF(E2=M2,"Same Reg",IF(ISBLANK(E2),"LD is Blank",IF(ISBLANK(M2),"TMC is Blank",IF(ISBLANK(M2),"TMC No Entry",IF(D2<>M2,"Different Reg"))))))

And it works!!! I needed this for work tommorrow....Thanks SO much!