Forum Discussion
How to return text after entering an associated number?
UPDATE: I'VE ADDED AN EXAMPLE SPREADSHEET
I have a database of location names an their corresponding location codes. Over time, people using the database have used slightly different spellings of the location names, which leads to inaccurate totals for each location because each spelling becomes a new entry. The location codes have never changed.
Can I use some from of an IF and VLOOKUP function to return the proper location name (text) in one cell after entering the associated location code (number) in a different cell?
Here's an example of the location names and their codes:
| Fish Ladder | 1519 |
| Ballard Marine | 1520 |
| NW Adhesives | 1521 |
| James Ranch | 1522 |
AKnott1620 All these below formula should work if I understand your question correctly.
=VLOOKUP(B2,Table7,2,0) =XLOOKUP(B2,Table7[Site Code],Table7[Site Name]) =INDEX(Table7[Site Name],MATCH(B2,Table7[Site Code],0))Recommended formula is to use XLOOKUP().
But if there are differently spelled location names with the same location code, how can we determine which one is correct?
Or do you have a list of unique location codes together with the correctly spelled location names?
- I have a list of unique location codes along with their correctly spelled location names, correct. I want to use this "master" list to fill in a column with the correctly spelled location names. Next to the blank column is a list of the location codes. I can upload an example.
AKnott1620 All these below formula should work if I understand your question correctly.
=VLOOKUP(B2,Table7,2,0) =XLOOKUP(B2,Table7[Site Code],Table7[Site Name]) =INDEX(Table7[Site Name],MATCH(B2,Table7[Site Code],0))Recommended formula is to use XLOOKUP().
