VLOOKUP to return "text"

Hi hlpm,

VLOOKUP will return text. Columns B and C of Data are text.

Formula in B2 =VLOOKUP(A2,Data::B:C,2,1)

What are you trying to do?

Regards,

Ian.

Hi hlpm,

Thanks for the 'greenie' and feedback.

Actually, I got it wrong. My formula looks for a close match

• close match (TRUE, 1, or omitted): If there’s no exact match, select the row with the largest left-column value that is less than or equal to the search value. If you use close match, you can’t use wildcards in search-for.
• exact match (FALSE or 0): If there’s no exact match, return an error. If you use exact match, you can use wildcards in search-for.

It should be

=VLOOKUP(A2,Data::B:C,2,0)

(zero to specify an exact match).

The formula works because there are only exact matches in my example 😊 (blush).

Regards,

Ian.

Hi hlpm,

Another way to control for an exact match is to use a popup of search terms that are reflected in the lookup table. I have a 200 item popup that was easy to make (used the table I search) and very quick to scroll thru on my mac.

quinn

And don't forget there is the versatile INDEX MATCH combination that avoids some of the limitations of LOOKUP and VLOOKUP.

You can specify exact or somewhat fuzzy (find value, find smallest, find largest) and the order of the columns in the lookup range can be whatever you want (i.e., unlike VLOOKUP the value doesn't have to be looked up in the leftmost column; it can be any column you specify).

Here, for example, you can look up the first name for a given last name, without moving last name to the leftmost column in your lookup range:

SG