MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup equivalent for text entries

Posted by Brian Cole on October 15, 2001 8:28 AM

Is there a function for text entries equivalent to vlookup for data values?

Objective: To have a table of about 20 different measurement units (e.g. mg,
mL, L, lbs, gal, grams, etc.)and adressable corresponding conversion factors.

Vlookup tables require that the lookup values be numbers not text. I need to
be able to assign a numeric value to a cell based on the text content of a
corresponding cell. The number of possible text entries is limited but greater
8, so nested IF statements do not allow for enough options. Also, the initial
letter of the text entries are not always unique, so the CODE function will
not always return a unique numeric code. For example: CODE("milligram") = 109
and CODE("milliliter") = 109

Posted by Iml on October 15, 2001 9:08 AM

I'm not sure I follow, but lookup values can be either text or number, as long as the first argument format matches the table? Forgive me if I am missing something.

Posted by Jerry on October 15, 2001 11:06 AM

You can use text in vlookup. In your case, the if statement will just need to enclose the text in quotes, i.e. "TEXT" or "milligrams" within the if statement.