Search 2 ranges of data for return value in a separate field

[sue.rob]

I need to search Col A (Engine) systematically and look if each value is contained in fields in C2 to H4a and return the value found in Field A8. (see below). Can this be done? There will always be only 1 value found.

Engine Gmud Ops.
LR6 AAA BBB LLL DDD EEE FFF
2H9 G21 M9P TH8 DD4 D4E 2H9
LD1 12I 67P 90I 80I 3IU 9KU
LD3
LA7

Value=


Hope you can help

Thanks






[ This Message was edited by: sue.rob on 2002-05-22 05:05 ]

[Asala42]

Hey,

Your post didn't seem clear to me.

I need to search Col A (Engine) systematically and look if each value is contained in fields in C2 to H4a and return the value found in Field A8. (see below). Can this be done? There will always be only 1 value found.

Engine Gmud Ops.
LR6 AAA BBB LLL DDD EEE FFF
2H9 G21 M9P TH8 DD4 D4E 2H9
LD1 12I 67P 90I 80I 3IU 9KU
LD3
LA7

Value=

If I understand you correctly, an easy way would be to type the following down Column I:
=COUNTIF($C$2:$H$6,C2)

Then in A8:

=INDEX($C$2:$I$6,MATCH(1,$I$2:$I$6,0),1)

Alternatively, since your only dealing with 5 rows, you could also use a nested if statement:

=IF(Countif($C$2:$I$6,C2)>1,C2,IF(Countif($C$2:$I$6,C3)>1,C3,IF(Countif($C$2:$I$6,C4)>1,C4,IF(Countif($C$2:$I$6,C5)>1,C5,C6))))

Hope that helps,
Adam

[ This Message was edited by: Asala42 on 2002-05-22 06:18 ]

[sue.rob]

Many thanks. I'll try it out now

[zzdave]

would a vlookup or hlookup do the job??