Hi all,

I am having problems devising a formula to do the following in cell A2
sheet1, i want the cell to lookup a match for A1 Sheet1 in a named
range on Sheet2 and return the value of the cell 3 columns away from
that cell.

So if A1 sheet1 contains the word "Test" i want the lookup to look for
"Test" in the named range (lets say "Find"), lets say "Test" is found
in E3 sheet2 then in A2 sheet1 i want the value of E5 sheet2.

The names are variable for the cells in sheet1 and sheet 2.

Hope you can help!

Simon

P.S i have tried =offset, =match, = index etc and having no joy!


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=465684

Have you tried the help index for LOOKUP?

--
Don Guillett
SalesAid Software

"Simon Lloyd"
wrote in message
...

Hi all,

I am having problems devising a formula to do the following in cell A2
sheet1, i want the cell to lookup a match for A1 Sheet1 in a named
range on Sheet2 and return the value of the cell 3 columns away from
that cell.

So if A1 sheet1 contains the word "Test" i want the lookup to look for
"Test" in the named range (lets say "Find"), lets say "Test" is found
in E3 sheet2 then in A2 sheet1 i want the value of E5 sheet2.

The names are variable for the cells in sheet1 and sheet 2.

Hope you can help!

Simon

P.S i have tried =offset, =match, = index etc and having no joy!


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=465684

yes Don,

I have tried the help index, my problem is the look up table is in no
particular order and is 12 columns across, and what i need is if a name
im looking for is found in one coulmn the return the value of the column
3 to the right of it, the lookup table is on sheet 2 and the value is to
be returned on sheet 1.

So i'm having difficulty, i can return a value with a standard Vlookup
but it wont do it for the whole named range, the name im looking for
has to appear in the first column or it doesnt work but i need it to
search the whole named range!

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=465684

Then I suggest a macro using FIND to find the cell and offset(0,3) to get
the value.

--
Don Guillett
SalesAid Software

"Simon Lloyd"
wrote in message
...

yes Don,

I have tried the help index, my problem is the look up table is in no
particular order and is 12 columns across, and what i need is if a name
im looking for is found in one coulmn the return the value of the column
3 to the right of it, the lookup table is on sheet 2 and the value is to
be returned on sheet 1.

So i'm having difficulty, i can return a value with a standard Vlookup
but it wont do it for the whole named range, the name im looking for
has to appear in the first column or it doesnt work but i need it to
search the whole named range!

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=465684