0

I have 3 sheets in Excel. In the 1st one there is a column with hotel names and another one with ID. I want to get that ID from the ID column on the 2nd sheet where I have the names as well.

The problem is that sometime the name on the 1st sheet is Mirsini and on the 2nd sheet is Mirsini Hotel. So the result of a VLOOKUP if set to false is #NA or in true it returns a wrong number.

Is there a way to exclude keywords somehow?

OR

A way to replace all the names to be the same without replacing one by one?

Improve this question
  • I dont get it, can you upload a example file or screenshot? – Bilo Jun 25 '15 at 15:21
  • Welcome to Super User! Please share the formula you are using so far. Also, what have you researched and attempted to resolve this? – CharlieRB Jun 25 '15 at 15:59
  • @Bilo It won't let me upload pictures as I have less than 10 reputation. Imagine 3 sheets. The 1st one is the master that contains 4 columns with SourceAid,SourceBid,SourceCid & SourceAhotelname. The 2nd file is the Source B file. With 2 columns SourceBid & SourceBhotelname. The same with 3rd sheet but SourceC – Tommy Georgiou Jun 26 '15 at 6:38
  • @CharlieRB First Formula was a Vlookup(SourceAhotelname,Sheet2!SourceBid$:SourceBhotelname$,1,false) The second formula was going to be =INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type)) My research was based on link and link – Tommy Georgiou Jun 26 '15 at 6:44
0

The best approach is to standardize your data. You can use the find/replace command in Excel, Find Mirsini, replace with Mirsini Hotel - but you have to make sure you don't replace Mirsini Hotel with Mirsini Hotel Hotel. This can be time consuming.

However, if the names in the two lists are fairly standard, you can write a nested Vlookup using a separate table to "translate" the alternate names. The caveat is that each entry in the first column of your "translation table" may have only one variation in the second column.

example: =VLOOKUP(VLOOKUP(E3,$A$3:$B$7,2,0),$I$3:$J$7,2,0)

the middle vlookup [VLOOKUP(E3,A3:B7,2,0)] looks up the entry from your first table in the translation table then looks for that in the second table and pulls your ID to the first table.

Improve this answer
  • it is time consuming to find/replace 6k names!! – Tommy Georgiou Jun 26 '15 at 7:12
  • @TommyGeorgiou I know it's time consuming that's why I suggested the "translation" table. How many variations do you have on a single hotel name? – AZ Lynx Jun 27 '15 at 3:14
  • In some cases up to 3. ie artium hotel and the other is artium beach hotel – Tommy Georgiou Jun 29 '15 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.