|
|
|
|
Max Wrote:
One play ..
Assuming data below is in col A, from row1 down
code1
111
112
code2
222
223
code3
333
334
etc
(Code#'s are assumed unique and the data structure is assumed regular)
Let's reserve B1 & C1 for inputs of the code# and the number of the
value to
be returned, i.e. a "1" to return the 1st value beneath or a "2" to
return
the 2nd value beneath the row with the matching code.
Put in B1, say: code2
Put in C1, say: 2
Put in D1:
=IF(OR(B1="",C1=""),"",IF(ISNA(MATCH(B1,A:A,0)),"N o matching
code",INDEX(A:A,MATCH(B1,A:A,0)+C1)))
D1 would return the required result, viz.: 223
Unmatched codes, if any, will return the alert: No matching code
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"giantwolf"
wrote
in message
...
Hi,
I have a query which I don't think can be solved by the use of either
h
or vlookup, I'm thinking a combination of both but I guess this
doesn't
exist.
I have a list of codes and these need to be looked up against a
table
where each code appears in the same column as the value I want to
return. eg
code1
value
value2
code2
value
value2
code3
value
value2
In each instance I would want to go down the list and return the
number
that appears in "value" directly under the corresponding code in
this
list when it is looked for from my lookup list. I don't think
hlookup
would work because I need to find each code below another rather
than
across and v lookup wouln't work because I need to find a value
below
rather than in an ajoining column. Any ideas?
--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile:
http://www.excelforum.com/member.php...o&userid=24718
View this thread:
http://www.excelforum.com/showthread...hreadid=394140
Thanks to both of you, they both do exactly what I wanted. Cheers.
--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=394140
|