Lookup with parts of strings

[Maniv]

Dear all

This problem is killing my time since long.. Vlookup works only on exact
matches or approximate matches. Choosing the TRUE (approximate matches)
option in the 4th argument of the function is fraught with danger as it might
throw unexpected and wrong results.

I have two data tables with KEY FIELD data as below

table 1 table 2
GUJ-Prayag Raj Complex, Kalol Prayag Raj
GUJ-Dhartimanan Complex, mehsa Dharti

Table 2 has the following structure

key field value required in table1
Dharti GUJMEH0355


Trying to populate the value GUJMEH0355 in a column to the right of the key
field in table1. vlookup is failing as it says 'Dharti' and 'GUJ-Dhartimanan
Complex, mehsa' are not matching strings!!!

Please help out..

 

[Ashish Mathur]

Hi,

You can try something like

=vlookup(*Dharti*,table_array,col_index,0)

Please note the * before and after Dharti

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Please ignore my mail mail. I misunderstood your question

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Francis]

this is not clear, you need to explain or elaborate more
show your data and the result you want
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Maniv]

Hi Francis.

Thanks for your interest in helping me out..

Table Array (the 2nd argument for vlookup function) is as below :

Site Name Site Id
Ashirwad Tower GUJAHM0027
Shivam GUJAHM0064
Shikhar Appt. - Ghodasar GUJAHM0100
Shaptrushi GUJANA0129
Adarshilla Comoplex GUJBAN0155
M J Plaza_Shihor GUJBHA0199
Harshile - GIDC GUJGAN0224


The data in SITE ID column against site name containing the word 'Shikhar
Appy' which is 'GUJAHM0100' needs to be populated in the target table whose
structure is as below :

Location Name Site Id
GUJ-Prakruti Apts, Parimal Cro
GUJ-Rushabh Palace, Sabarmati
GUJ-Rudra Prayag, Nr. Ramdevna
GUJ-Saath Sangath, Satellite
GUJ-Buena Vista, University Rd
GUJ-Vasupujya Flats, Vejalpur
GUJ-Shikhar Appt, Nr Cadilla B GUJAHM0100