Hi all,

I would like to find a value in a named range and return it in a cell
if this criteria exists....sheet1 A2 if named range value = A1 then
return value from 3rd column away from match same row

So if A1 Sheet1 contains "test" and named range (lets say "Find")
Sheet2 contains "test" (lets say in E2) then A2 Sheet1 should show the
value of E4 Sheet2.

Dont know whether that is clear but its been driving me nuts as i have
tried the Match, Index and Offset functions but without
success..........Can you Help?

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=465487

Try VLOOKUP...See the Help files for details on its use.

=VLOOKUP(A1,FIND,3,0) where A1 is the cell containing the value to
look for, 'FIND' is the range name to look in for the value in A1, '3'
is the column within the range to return the value if lookup is found,
'0' says to find an exact match

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=465487

thanks for your reply swatspop,

I have one problem with that ( as i tried it and it didnt seem to work)
and its that the named range encompasses 12 columns and 40 rows, it was
not possible to have all the range in 3 neat rows.

Hope you can help,

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=465487

My understanding was that your 'named range' encompassed those 12
columns and 40 rows. Vlookup searches the first column in your 'range'
for the listed value. When found, the value in the identified column is
returned.

Is your 'FIND' range set up this way? If not, you would need to either
name the entire range or list the entire range in your formula. As
such:

=VLOOKUP(Sheet1!A1,Sheet2!A1:L40,3,FALSE)

where A1 is on Sheet1, and you are looking to return the value in
column C on Sheet2 from the same row that contains somewhere in column
A the same value as Sheet1!A1. note the FALSE part of the formula says
that if an exact match for A1 is not found, return #N/A.

I hope this is what you are trying to do.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=465487

Thanks again Bruce,

I seem to be making an omission when i'm explaining, i have names on
Sheet 2 in column E then 2 columns of data i then have names in column
H then 2 columns of data.......ect and so on until last column of data
in column P, on Sheet 1 i have groups of names (all of which appear on
Sheet 2) and in the cell to the right of each name i want to return the
value of the cell on Sheet 2 two columns away, so if it was Column E2
then the value of G2 will appear on Sheet 1.

Sorry for any cloudiness but i knew what i was trying to achieve but
the fingers wouldnt let me explain here!!!!!

I was currently trying
this...=IF(ISNA(VLOOKUP(A4,Find,3,FALSE)),0,(VLOOK UP(A4,Find,3,FALSE)))
as i need to return a value of zero if the name is not found or there is
no value in the cell we are looking to extract from.

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=465487

Now it sounds like you actually have 4 tables of data, each having 3
columns (E-G, H-J, K-M, N-P). VLOOKUP can only work on 1 table (at a
time). It will always look in the first column of the table.

I can only think of one solution based on your current layout. That
would be to name each block of 3 columns with a different name (e.g.
FIND1, FIND2....) and adjust the formla as such:

=IF(NOT(ISNA(VLOOKUP(A4,FIND1,3,0))),VLOOKUP(A4,FI ND1,3,0),IF(NOT(ISNA(VLOOKUP(A4,FIND2,3,0))),VLOOK UP(A4,FIND2,3,0),IF(NOT(ISNA(VLOOKUP(A4,FIND3,3,0) )),VLOOKUP(A4,FIND3,3,0),IF(NOT(ISNA(VLOOKUP(A4,FI ND4,3,0))),VLOOKUP(A4,FIND4,3,0),0))))

This will then search each table in turn for the value in A4, returning
0 (zero) if the value does not appear 'exactly' in any of the 4 tables.

Are we getting closer?

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=465487