To lookup the B column, knowing the A value:
=INDEX(Sheet1!$A$1:$B$4,MATCH(A1,Sheet1!$A$1:$A$4, 0),2)
However, a VLOOKUP would be simpler
To lookup the A, knowing the B
=INDEX(Sheet1!$A$1:$B$4,MATCH(B2,Sheet1!$B$1:$B$4, 0),1)

To avoid seen N?A when there is not match, wrap in an IF
=IF(COUNTIF(Sheet1!$A$1:$A$4,A1),INDEX(Sheet1!$A$1 :$B$4,MATCH(A1,Sheet1!$A$1:$A$4,0),2),"")
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"andrewd" wrote in message
...
Hi Bernard,
i don't think the page contains answers to my query.

I want to be able to make a sheet where i can enter either my product code
in column A or the manufacturers code in column B. if the code is entered
in
column A i want it to lookup and return the manufacturers code in Column
B.
if i enter the manufacturers code in column B i want it to lookup and
return
my code it column A. if there isn't a number in either it should do
nothing.
The lookup sheet contains a full list of product codes... mycode in column
A
and man code in column B

So sheet 1 (master data)
Column A Column B
AAA111 BBB111
ABC111 BCD222
ABH111 BHJ333
AUI111 BYU444


and on sheet 2 we would have

Column A Column B
AAA111
BYU4444
ABH111
BCD222
AUI111

So i want the other column to be filled in by a function of some sort.

Hope this makes sense.

Thanks
Andrew

"Bernard Liengme" wrote:

This is yet another topic that Chip has a complete answer for: see
http://www.cpearson.com/excel/lookups.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"andrewd" wrote in message
...
these are hard to explain with out examples..

i want run a lookup from two different columns. if there is a value in
column A i want it to lookup the value for column B. but if the is no
value
in column A i want to lookup the value for Column A using the value in
column
B. i want to do this without the formula just looping round.

Column A and B are linked in the the lookup spreadsheet.

Hope this enough for someone to help.