For the first case you can use VLOOKUP:
=VLOOKUP(A1,Sheet2!A:B,2,FALSE)
For the second INDEX and MATCH
=INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0))
You can combine the two in a single formula, with some error control, like:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),IF(ISNA(M ATCH(A1,Sheet2!B:B,0)),"Not
present",INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0))) ,VLOOKUP(A1,Sheet2!A:B,2,FALSE))

Hope this helps,
Miguel.

"blain" wrote:


Is it possible to set up a VLOOKUP that works something like this?

Enter value in sheet1!A1

check this value in sheet2!A:B

If value is found in A return value in B to sheet1!A2
If value is found in B return value in A to sheet1!A2


--
blain
------------------------------------------------------------------------
blain's Profile: http://www.excelforum.com/member.php...o&userid=34368
View this thread: http://www.excelforum.com/showthread...hreadid=552916