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

Hi,

Try,

If your data are in A1:A10 then,

=INDEX($A$2:$A$10,MATCH(C2,$A$2:$A$10,0)+1)

where C2 houses the code

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=394140

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

Krishnakumar Wrote:
Hi,

Try,

If your data are in A1:A10 then,

=INDEX($A$2:$A$10,MATCH(C2,$A$2:$A$10,0)+1)

where C2 houses the code

HTH


Superb, thanks!


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=394140

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

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"giantwolf" wrote:
....
Thanks to both of you, they both do exactly what I wanted. Cheers