Vlookup question!

[Nordicrx8]

Hey All!

Was hopping you could help me really quick! My boss asked me to compare two worksheets with very similar data, and find the variences. I know vlookup is my best bet here, but I haven't used it in a while and I can't get it to work properly. Please help - she needs this by EOD!

The workbook has two tabs:
Tab one: Trending
Tab Two: Forecast

Both worksheets have the same columns:

A: Name
B: Amount

I need to find any differences from the two lists, including missing names.

Here is the formula I am using, but it doesn't seem to be working properly:

=VLOOKUP($A2,Trending!A:B,2,FALSE)

Any help would be GREATLY appreciated!!!

Thank you!!

 

[PatOBrien198]

If you've got that formula on the "Forecast" sheet, it should work.

It will error out (#N/A) if the name on the Forecast sheet is not found on the Trending sheet.

You'll need to do a similar formula on the Trending sheet (pointing to the Forecast sheet) .

You could make the results a bit more helpfull if you used a formula such as:

=IFERROR(VLOOKUP($A2,Trending!A:B,2,FALSE)-B2,"Missing")

Then you could filter on values other than 0, to find differences and filter on "Missing" to find, well, the missing ones.

 

[RobertSF]

If it works for the first row but not for the rest of the rows, I'm pretty sure it's because your formula has the absolute and relative reference reversed.

=VLOOKUP($A2,Trending!A:B,2,FALSE) 'no
=VLOOKUP(A2,Trending!$A:$B, FALSE) ' should work

If the formula is correct, you'll get the cell returned when there's a match, and a #N/A when there isn't. You probably know this. What you may not realize is that you have to do a VLOOKUP on both sheets. This is because VLOOKUP can tell you what on this sheet doesn't match that sheet, but it doesn't tell you what on that sheet doesn't match this sheet.

I hope this helps.

 

[Nordicrx8]

Hmm - I still can't get it to work properly for some reason. Any way I can share a sample file for you guys to view?

Thank you!

 

[RobertSF]

It might be more helpful is you said how it's not working. I don't know if one can post files here. I'm new myself.

 

[Nordicrx8]

It might be more helpful is you said how it's not working. I don't know if one can post files here. I'm new myself.

Here is a link to a sample file. Essentially, when I use vlookup to reference the same tab, it works. But when I use it to reference the other tab, I get an NA, even though I know there is a match on the other worksheet.

Thanks for all the help!

https://drive.google.com/open?id=0B1gew_d-9kZaLUw4bGhwNVlrdWs

 

[FormR]

HI get an NA, even though I know there is a match on the other worksheet.

Hi, but they do not match - for example in the trending sheet you have:

"1120 PROFESSIONAL CENTER LLC"

And in the Forecast sheet it's:

"1120 PROFESSIONAL CENTER LLC-Retention"

Maybe you'll have more luck with:

=VLOOKUP(A1&"*",Forecast!A:B,2,0)

 

[Nordicrx8]

Hi, but they do not match - for example in the trending sheet you have:

"1120 PROFESSIONAL CENTER LLC"

And in the Forecast sheet it's:

"1120 PROFESSIONAL CENTER LLC-Retention"

Maybe you'll have more luck with:

=VLOOKUP(A1&"*",Forecast!A:B,2,0)

Urgh - that would explain it! I wasn't even thinking about that! Thank you!!!