formula for finding a value!

[Simon Lloyd]

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!

[Don Guillett]

Have you tried the help index for LOOKUP?

--
Don Guillett
SalesAid Software
donaldb@281.com
"Simon Lloyd" <Simon.Lloyd.1v08uf_1126123537.816@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.1v08uf_1126123537.816@excelforum-nospam.com...
>
> 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
>

[Simon Lloyd]

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

[Don Guillett]

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"Simon Lloyd" <Simon.Lloyd.1v0eej_1126130732.1631@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.1v0eej_1126130732.1631@excelforum-nospam.com...
>
> 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
>