Combination of H & Vlookup??

G

giantwolf

Hi,

I have a query which I don't think can be solved by the use of either
or vlookup, I'm thinking a combination of both but I guess this doesn'
exist.

I have a list of codes and these need to be looked up against a tabl
where each code appears in the same column as the value I want t
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 numbe
that appears in "value" directly under the corresponding code in thi
list when it is looked for from my lookup list. I don't think hlooku
would work because I need to find each code below another rather tha
across and v lookup wouln't work because I need to find a value belo
rather than in an ajoining column. Any ideas
 
K

Krishnakumar

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

HT
 
M

Max

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)),"No 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
 
G

giantwolf

Krishnakumar said:
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
Click to expand...


Superb, thanks!
 
G

giantwolf

Max said:
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 th
value to
be returned, i.e. a "1" to return the 1st value beneath or a "2" t
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)),"No 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
Click to expand...

Thanks to both of you, they both do exactly what I wanted. Cheers
 
Top