Need help with vlookup

[redhat]

Hi all,

Trying to use vlookup to fetch a column based on matching string. Not sure if can be done with vlookup, so far no luck.

My target is I want to grab the emails for Tom, John and Jarry in Excel file1 under header2 when the value is Red in Excel File2 under header2.

How I can put that IF or Match condition inside vlookup?

So that the file will look like:

header1 header2
-------- ---------
Tom [email protected]
John [email protected]
Jarry [email protected]


Current Excel file1 has sheet1 which has one column as
header1 header2
-------- ---------
Tom
John
Jarry


Excel File2 has sheet1 with 3 columns as

header1 header2 header3
---------------------------------
Tom Red [email protected]
John Black [email protected]
Jarry White [email protected]
Tom Blue [email protected]
John Red [email protected]
Jarry Red [email protected]



Thanks in advance.

[sandy666]

Maybe use INDEX() and MATCH() functions, will be easier

[redhat]

=VLOOKUP(A2,[Book2.xlsx]Sheet1!$A$2:$C$7,3,0) gives me the 1st match and also can't put the condition to look only for Red.

Will take a look at the match and index. But any example would be a great help.

thanks.

[kvsrinivasamurthy]

Try this ARRAY formula, to be confirmed with Ctrl+Shift+Enter together.

Please Login or Register  to view this content.

[redhat]

Got 0 in the cell.

excelerr.jpg

[AliGW]

We can't troubleshoot a screenshot - please upload the workbook.

[kvsrinivasamurthy]

Are you searching only for Tom Or Tom& Red. Pl Upload file showing the required results.

[redhat]

Perfect, thanks a lot PMiafzal555 and everyone.

That was what I was looking for.

Thanks.