VLookup Help...

September 20, 2011 at 06:14:48
Specs: Windows 7, 2.3Ghz / 4G RAM
A friend of mine needs to learn how to use vlookups for work, and since I'm quite computer literate she asked me to help her with it, but since I'm not well up on office software I've been finding it a bit of a struggle. I've included a link to the document that I'm trying to work on,


and what she wanted to do was...

On sheet1 in column B there is a list of numbers which she wanted to be able to use a vlookup to quickly check if any of these values are on sheet2 in the CWO no's on column C, sometimes these sheets are massive, and they sometimes have many sheets to check so it's just not realistic to check them all buy hand.

The vlookup that I've already tried seems to return everything except the expected value, I'm obviously doing it wrong somewhere, but I can't figure out where.

Thanks in advnced, ...John...

See More: VLookup Help...

Report •

September 20, 2011 at 17:55:26
I'm not sure what you are trying to do.

Are you just trying to see if the values in Sheet1!B:B exist in Sheet2!C:C or are you trying to return a value from some other column of the big table in Sheet2 if a match is found between Sheet and Sheet2?

You've got VLOOKUP formulas that contain #REF! errors on Sheet1 and a really strange looking VLOOKUP formula on the sheet named VLOOKUP.

I'm really confused as to what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 20, 2011 at 18:50:00
Yes, I'm trying to see if the values in column B in sheet1 exist in column C in sheet2, I don't know if you can add a vlookup to a cell that already has data so that's why I created the vlookup sheet, I was hoping to paste the values from sheet1 in the first column in the first column of the vlookup sheet and if that value exists I was hoping it would be displayed in second column on the vlookup sheet.
If that value didn't exist I was hoping it would show some sort of error or even nothing at all.

Maybe this isn't the best way to do it, but I couldn't think of any other.

I've worked through some vlookup tutorials, and I can seem to get them to work in other examples, eg. showing prices for certain products, being able to find out peoples birthday's, but for some reason I just can't seem to apply any of that to this example, I'm probably using it completely wrong but I just can't figure out what I'm doing wrong.

Thanks ...John...

Report •

September 21, 2011 at 04:48:08
re: I don't know if you can add a vlookup to a cell that already has data

You can't have a formula in a cell that contains hard coded data. It's one or the other: A formula or hard coded data.

In the spreadsheet I downloaded, there appears to be an issue with the values in Sheet2!C:C. The are left justified, which typically means that Excel is not seeing them as numbers but as some type of text string. That means that they do not match the values in Sheet1!B:B, even if they look the same.

You can prove that to yourself this way:

Sheet1!B409 contains 1004858
Sheet2!C21 contains 1004858

Therefore this formula should return TRUE, but instead it returns FALSE.


Now, if you double click in Sheet2!C21 and then click out of it, the value will become right justified and the result of the formula above will change to TRUE. That is because the editing action you performed in the cell caused Excel to re-format the value to be a number.

I've seen this happen in Excel many times before when data is imported from external sources. It's a pain, but it happens.

You can fix this as follows:

Select any empty cell
Press Ctrl-C to copy it
Select all of the values in Sheet2!C:C
Right-Click or click the Edit menu
Choose Paste-Special
In the Operations section, click the radial button next to Add
Click OK

This will force Excel to Add "zero" to the values and turn them into numbers.

Now you can try your VLOOKUP formulas to find matches.

If you are still having problems with VLOOKUP after performing the steps above, come on back and we'll see what other help we can offer.

BTW...if all you want to do is highlight matching values, you can use Conditional Formatting.

See response 3 & 4 in this thread:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question