Vlookup Function

[Linda]

Hi,

I have 2 large data spreadsheets and want to
compare/match data between the 2 files. For example both
spreadsheets contain columns which have an invoice number
where the number may contain letters, hyphens etc.

I want to know if there are exact matches between the 2
files and can not figure out the syntax to write for the
desired results. Can anyone help with the example below?

File # 1 contains the listed invoice #'s and other
columns of acssociated date. File #2 a different list of
invoice #'s and the additional columns of data. Both
spreadsheets, the invoice # are in column A.

In this small example there are exact matches,some that
do not match, and duplicates, but my results are not what
I expect, even when I combine the 2 files into one
spreadsheet. =vlookup(E2,(A2:C2,1) E2=data I want to
match, A2:C2=range of data to look in and 1=the column #
to list the result of an exact match. I have added
the ,FALSE to the end but ended up with even more
confusing results.

I would greatly appreciate any help or suggestions.
Thanks.

Inv # File 1
I0001331
I0001341
I0001341
I0001363
I0001363
I00041627
I0004350-IN

Inv # File 2
I0001331
I0001341
I0001363
I0003585
I0005167
I0005209
I0005581

 

[Guest]

Hi Linda,

If I understand your objective correctly, all of the variables are fine except the Range of the lookup table (the second variable in the vlookup function). It should go from the beginning of the table to the end of the table. In other words, that A2:C2 range should go something like A2:C1000 (assuming your lists ends at row 1000). Hope this helps.

 

[Guest]

Vlookup() is a vertical lookup routine. So if you want to use it, you need your data arranged in a columnar table. From what you have said, that is what you've got. But then your formula has a little horizontal scrap for your lookup range, so I think your formula is probably wrong.

I think you are trying to find an exact match for the data in Column E somewhere in Column A. You can do this with Vlookup, but that then returns some data on the same row as your exact match. It sounds to me like you just want to know there is a match.

Lets assume you have 100 rows of data. Try this:

=MATCH(E2,$A2:$A101,FALSE)

This can be copied down your column in (say) column F. E2, will become E3 and E4 etc in the cells beneath, but $A2:$A101 will be fixed, because that is the range you need to look in, and it is the same in each case. FALSE, says look for an exact match.

The formula returns an integer indicating which row in the range the exact match was found, or #N/A if no exact match is found.

VLOOKUP works exactly the same way, but returns data on the same row as the where the match was found, so if you need to find an exact match, and return (say) Invoice Value from column C you would use a formula like this:

=VLOOKUP(E2,$A2:$C101,3,FALSE)

Here the "3" says "return the result from the third column of the lookup range on the same row as the exact match". If no exact match is found, you'll get #N/A again.

Hope this helps.

 

[linda]

Hi,
Thanks for the help. I did mistype the range in my e-mail
below. In my file I do have the entire range A2:C8 and I
have tried False.

I extracted a small portion of data from both spreadsheets
as a test for this vlookup formula, and the syntax is
exactly as listed in your note below. I also tried
the "match" but received the same result.

I am sure there is a simple solution and something I am
overlooking. Thanks for all your help.

Linda

-----Original Message-----
Vlookup() is a vertical lookup routine. So if you want to

use it, you need your data arranged in a columnar table.
From what you have said, that is what you've got. But then
your formula has a little horizontal scrap for your lookup
range, so I think your formula is probably wrong.

I think you are trying to find an exact match for the

data in Column E somewhere in Column A. You can do this
with Vlookup, but that then returns some data on the same
row as your exact match. It sounds to me like you just
want to know there is a match.

Lets assume you have 100 rows of data. Try this:

=MATCH(E2,$A2:$A101,FALSE)

This can be copied down your column in (say) column F.

E2, will become E3 and E4 etc in the cells beneath, but
$A2:$A101 will be fixed, because that is the range you
need to look in, and it is the same in each case. FALSE,
says look for an exact match.

The formula returns an integer indicating which row in

the range the exact match was found, or #N/A if no exact
match is found.

VLOOKUP works exactly the same way, but returns data on

the same row as the where the match was found, so if you
need to find an exact match, and return (say) Invoice
Value from column C you would use a formula like this:

=VLOOKUP(E2,$A2:$C101,3,FALSE)

Here the "3" says "return the result from the third

column of the lookup range on the same row as the exact
match". If no exact match is found, you'll get #N/A again.