Excel 2007 : Vlookup NOT WORKING!

[jennbates]

Hi there, my first time posting. I'm a basic user of vlookups, but I'm good at what I know about them. I cannot get this formula to work, however. Here's what I've tried:
1. Excel Options > Formulas > Workbook Calculation > Automatic.
2. Formatted my 'variable' (is that the right word to use?)columns to be the same, and I've tried text, general, and number. None work!
3. I've tried selecting one of the variable columns and clicked text-to-columns, then repeated that with the other variable column, no success.
4. I've tried copying and pasting both chunks of data into a clean and fresh spreadsheet, no luck.

I'm sprouting gray hair as I type this... and would be completely grateful for any help.

A million thank-you's for whoever can solve this for me! Spreadsheet is attached (if I did it correctly).

Thank you,
Jennifer
hot pumps_040711.xlsx

[Cutter]

The formula you have in H4 is:

=VLOOKUP(A4,$K:$U,7,FALSE)

The value in A4 is:

1805398C93

That value does not appear in col K. So the result is #N/A.

Where is it that you think that A4 value is?

[Cutter]

You appear to be wanting the INDEX() & MATCH() formula.

If you are trying to get the WO# from column M that coincides with the Item # in col Q (that is a match for the Value in col A) then it would be:

=IFERROR(INDEX(M:M,MATCH(A4,Q:Q,0)),"") in H4 and copied down.

That, however, produced nothing but blanks so I checked your data in col Q and found that the entries had 20 spaces following the visible data.

When trimmed and replaced there were some results from the above formula but not all returned a value.

[jennbates]

The formula you have in H4 is:

=VLOOKUP(A4,$K:$U,7,FALSE)

The value in A4 is:

1805398C93

That value does not appear in col K. So the result is #N/A.

Where is it that you think that A4 value is?

In this range: $K:$U (specifically, column Q). As it turns out, that pump, 1805398C93, is not in column Q, so I would expect a #N/A value, but check out pump # MM102, which is in both column A and Q.
Is my formula wrong?

I thought I was asking it (the excel God's) to look up the values in column A through the range of cells (K:U) and return the value in the 7th column in that range, which, in the case of pump MM102, would be 216131. And I just noticed an error in my formula: Should read: =VLOOKUP(A4,$K:$U,3,FALSE)

Thank you!!

[jennbates]

You appear to be wanting the INDEX() & MATCH() formula.

If you are trying to get the WO# from column M that coincides with the Item # in col Q (that is a match for the Value in col A) then it would be:

=IFERROR(INDEX(M:M,MATCH(A4,Q:Q,0)),"") in H4 and copied down.

That, however, produced nothing but blanks so I checked your data in col Q and found that the entries had 20 spaces following the visible data.

When trimmed and replaced there were some results from the above formula but not all returned a value.

Ok, we're getting somewhere! Thank you! How do you trim the spaces? And how did you know there were spaces when they weren't visible in the formula bar? Thank you!

[Cutter]

Apart from the formula being wrong your data in col Q is messed up.

Click on cell Q71 which 'looks' like a match to MM102. Now, with that cell selected, click in the formula bar well to the right of the MM102 you see there. Notice how far to the right the cursor goes. Now backspace in the formula bar to reach the 2. That's how many spaces you have in that cell and the value in col A doesn't have spaces so - No Match.

[Cutter]

Here is your file with the data in col Q trimmed. I used the TRIM() function then Paste Special - Values to replace what you had in col Q.

[jwright650]

That's how many spaces you have in that cell and the value in col A doesn't have spaces so - No Match.

Ok...now I understand why the VLOOKUP returned a WO# for the wrong item#....I got WO#220412 for item in A6, and it should have been WO# 212824.

[jennbates]

Thank you so much - I'm truly grateful for your help, plus I'm grateful for the new formula you taught me.

Jennifer

[Cutter]

Your original formula:

=VLOOKUP(A4,$K:$U,7,FALSE)

was telling Excel to look for the exact (because you used FALSE) value held in A4 within col K. Where it finds it in that column get the value that is 6 columns to the right. (It is 6 columns to the right because it counts K as 1, L would be 2, M would be 3, etc so Q would be 7).

VLOOKUP() can not look for values to the left.

Here are a couple of links for VLOOKUP()

http://www.excelfunctions.net/ExcelVlookup.html
http://www.excelfunctions.net/Excel-...-Tutorial.html

[jennbates]

I guess I have one more quick question for you. When I import this data from our data base, I'm getting those leading spaces. Is there anything I can do, from an import standpoint, that would eliminate that, or do I need to TRIM() every time?

Again, thank you.

[Cutter]

Text to Columns - Delimited using the space character as the separator should do it for you.

Don't forget to mark your thread as SOLVED if that wraps it up for you.


Thanks for the "scales tap".

[jennbates]

Thank you - and will do!

[tm_butts]

Try inserting a column and do a simple calc if the lookup column is an actual #, i.e. number in column A * 1 and format the inserted column as number if not already formatted that way.

[FDibbins]

tm_butts, welcome to the forum

Dont feel too bad, but this thread is 3 years old, and is marked as solved. I doubt they are still following it