VLOOKUP Formulas

[Guest]

I'm not getting the desired result using VLOOKUP. I have a multiple page
workbook. In the first page (where my formula lives), Column I contains a
text string (such as STRQ321). In the second workpage, Column A contains
text strings ranging from STRQ1 to STRQ601. Columns B and D contain
information I want to have copied into the first workpage. I'm doing a
conditional lookup (if the cell in column L says "Y", I do the lookup,
otherwise I enter the text "N/A" in the cell). My equation is as follows:

=IF((L6="N"), "N/A", VLOOKUP(I6,'STRQ Req Summary'!A:F,2,FALSE))

Excel correctly evaluates I6 as the string "STRQ321". When it goes to the
referenced page VLOOKUP comes back with an error (#N/A). If I change FALSE
to TRUE, VLOOKUP returns the information from the line with "STRQ320". I
temporarily changed the return information from VLOOKUP to be the information
contained in Column A of the second worksheet -- this means that I should
have the same value from the lookup as is in I6, however I get #N/A when I
have FALSE and STRQ320 when I have TRUE. I have changed the type of data in
both columns to "Text", "Number" and "General" with no change in the results.
I have sorted the data in the second sheet, again with no change in the
results.

This result happens for every entry within the worksheet as I attempt to do
lookups of data.

Any ideas?

 

[George Nicholson]

Just a guess: Look for a trailing space(s) after your entries (in either,
but not both, lists). A trailing space wouldn't change the sort order, but
it could prevent an exact match (#N/A when FALSE/Exact and the 'previous'
list value when TRUE/Approximate).

HTH,

 

[Guest]

Gayle,

It sounds like you might have a space after each of the values in Column A
on the 2nd page. What would then happen is that FALSE will not find an exact
match (because of the space). However TRUE will go down the list until
Column A is alphabetically higher and then give the last value that was not
higher.

 

[Guest]

Thank you George and Art. The second workpage did have trailing spaces after
the text strings. The problem is now solved.