comparing values and returning different column

In the example of data that I initially supplied the data in sheet2 did not have any values that matched sheet1. I upated the example but still need help with the formula. I don't want to get rid of the the first 3 digits.

The data in both sheets have the same formating. So that shouldn't be the problem.

I almost have it in the initial formula, except it only returns column 2 in sheet2 when the information matches the first record in the sheet2.


Thanks the input of information thus far from everyone.

VLOOKUP is definitely the function that you need. It's just a matter of applying it.

What exactly is it doing incorrectly? Are your range set correctly? Did you absolute (use the $ sign) the necessary ranges? (It should look like: =VLOOKUP(B2,SHEET2!$A$1:$A$100,2,FALSE). )

Other than that, I would again recommend posting a sample of your spreadsheet (use dummy data if your info is confidential).

I get the #ref! error when I use the Vlookup function as outlined in the previous post. I have attached an example of the data.

I see the error, and I initially advised you incorrectly.

Your lookup range has to encompass all of the data you are looking in. You have:

=VLOOKUP(B2,Sheet2!$A$1:$A$2000,2,FALSE)

It should be

=VLOOKUP(B2,Sheet2!$A$1:$B$2000,2,FALSE)

Notice the lookup range has changed from A1:A1000 to A1:B1000

Try VLOOKUP again but with this small modification

=VLOOKUP(B2,SHEET2!$A$1:$B$100,2,FALSE).

The previous example was trying to look in the second column of a single column array!
Hence the #REF error.

Mark.

My typing is too slow.
rolleyes:

I now understand the error and indeed the vlookup function is working perfectly. Thanks for all the information.


JustMe602

Originally Posted by Mark@Work

My typing is too slow.
rolleyes:

Your typing isn't too slow. You just chose to use the red font to display the error, which gave me the slight advantage.