If vlookup does not MATCH then ONLY get the column value

[hsandeep]

I am having 2 workbook. I want to vlokkup / extract the relevant column values if Lookup_value DOES NOT MATCH.
Ex:
Workbook1
A1=PPP-00099
Workbook2
C1=PPP-00088 E1=786
In F1 (of Workbook2), answer would be 786 since C1 DOES NOT MATCH with A1.
If matched, answer required="" (null).
How to accomplish?

 

[Robert Mika]

I am having 2 workbook. I want to vlokkup / extract the relevant column values if Lookup_value DOES NOT MATCH.
Ex:
Workbook1
A1=PPP-00099
Workbook2
C1=PPP-00088 E1=786
In F1 (of Workbook2), answer would be 786 since C1 DOES NOT MATCH with A1.
If matched, answer required="" (null).
How to accomplish?

Assumnig your data are in Sheet1 on each workbook and you are compering row by row
=IF(A1<>'[Workbook 2.xlsx]Sheet 1'!$C$1,'[Workbook 2.xlsx]Sheet 1'!$E$1,"")

 

[hsandeep]

But even if A999 (of Workbook1)=PPP-00088 i.e. MATCHES with C1 (of Workbook2), then answer F1=786

 

[Peter_SSs]

I've just done this in a single sheet but if it does what you want you should be able to adapt to your different workbook scenario.

Formula is copied down.

Excel Workbook
	A	B	C	D	E	F
1	PPP-00099		PPP-00088		786
2	PPP-09872		PPP-09777		123	123
3	PPP-09873
4	PPP-00088
5	PPP-00003
6
Check for match

 

[hsandeep]

Formula works fine but I am FORCED to keep open Workbook1 else I get #VALUE! in Workbook2 !!!

 

[Peter_SSs]

Formula works fine but I am FORCED to keep open Workbook1 else I get #VALUE! in Workbook2 !!!

Try this

Open both workbooks.

In Cell F1 of Workbook2 put this formula

=IF(ISNA(MATCH(C1,'[Workbook1.xlsx]Data List'!$A$1:$A$1000,0)),E1,"")

Adjust the name of Workbook1 and the relevant sheet name in this formula if they are not what I have above.

Copy the formula down.

You can now close Workbook1 and the formulas should still work. Note that when you close Workbook1, the formulas in column F of Workbook2 will change to reflect the full path and name of Workbook1.