Vlookup first two words in the cell

[nageshpolu]

could some one help me how to do vlookup if i want to matchc first two words in the cell

for example
allianz insurance corp = allianze insurance

[zbor]

Instead of simple =VLOOKUP(A1, table, 2, FALSE) use:

=VLOOKUP(TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",255),2),255)), table, 2, FALSE)

[nageshpolu]

No it didnt solve my problem

iferror(index(slx!$d:$d,match($c4&"*"&$d4,slx!$b:$b&slx!$j:$j,0),),(index(slx!$d:$d,match($c4&"*"&$d4,slx!$c:$c&slx!$j:$j,0),)))

i m using this array to match... But i need to match atleast first two words so that it can blindly take it

[zbor]

can you upload example workbook?

[nageshpolu]

SLX-test.xlsxSLX-test.xlsx


please check the automated sheet

i took the reference from SLX sheet, now i need to match the account name by first two words ...

please help me out

[zbor]

So you will write Adare On and you want to search for Adare On Demand Communications Ltd (for one criteria)?

[nageshpolu]

may be yes

example: adarsh, there are many accounts on the name adarsh, so what i want is to match adarsh "second word" should be considered automatically while searching, which means if i give adarsh developers ltd it should search for adarsh developers and give the matching result

[[email protected]]

Hi...I think zbor has given the right formula. You just need to change referances.

=VLOOKUP(TRIM(LEFT(SUBSTITUTE(C4," ",REPT(" ",255),2),255)), SLX!B:D, 3, FALSE)

[nageshpolu]

no its not working

and moreover i need this reference in array which i have used, as i need to match two cells with two columns in the other sheeet which is not possible through vlookup, so plz help how to search with the given array
i have used wild cards but still we cant match first two words ...s o please help me

[zbor]

I've put here with your result also another approach.
Looks they both return same result.
Can you check solutions and tell us if some of them need to return another answer?

[nageshpolu]

actually all the accounts are existing in slx sheet. but we missed out some accounts and are displayed as #N/A ... so i need a forumla where i get 100 % result

thank you