VLOOKUP Query

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

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(MATCH(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
>
>

Thank you very much