formula for finding a value!

S

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!
 
S

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
 
Top