Dear Sir,

I know that VLOOKUP can look for only the first or the last data for
identical references by way of choosing the fourth argument as TRUE or FALSE.

May I know if there is a way to search for all the data in a string with the
same references as illustrated below ?

What formula must be I key in at B13 and copy downwards to get the answer ?

A B
Table A
1 BTH100 85
2 BTH100 68
3 BTH100 27
4 BTH100 45
5 BTH103 320
6 BTH103 141
7 BTH108 278
8 BTH120 220
9 BTH120 145
10
11
12 Table B
13 BTH100 85
14 BTH100 68
15 BTH100 27
16 BTH103 320
17 BTH103 141
18 BTH103 N/A
19 BTH108 278
20 BTH120 220
21 BTH120 145


Thanks

Low

--
A36B58K641

Hi,

Put this in B13 and the value you are looking for in D1

=INDEX($A$2:$B100,SMALL(IF($A$2:$B100=$D$1,ROW($A$ 2:$B100)-ROW($A$2)+1,ROW($B100)+1),ROW(A1)),2)

If you drag down it will find the first, second etc instance of your data
and relutn the value next to it in column B. Its an array so Ctrl+Shift+enter

it will start producing #Ref! errors when it fails to find a match so stop
dragging.

Mike


"Mr. Low" wrote:

Dear Sir,

I know that VLOOKUP can look for only the first or the last data for
identical references by way of choosing the fourth argument as TRUE or FALSE.

May I know if there is a way to search for all the data in a string with the
same references as illustrated below ?

What formula must be I key in at B13 and copy downwards to get the answer ?

A B
Table A
1 BTH100 85
2 BTH100 68
3 BTH100 27
4 BTH100 45
5 BTH103 320
6 BTH103 141
7 BTH108 278
8 BTH120 220
9 BTH120 145
10
11
12 Table B
13 BTH100 85
14 BTH100 68
15 BTH100 27
16 BTH103 320
17 BTH103 141
18 BTH103 N/A
19 BTH108 278
20 BTH120 220
21 BTH120 145


Thanks

Low

--
A36B58K641