index match

A B C D
ROW 5 1 65 6 56
ROW 6 2 100 7 41
ROW 7 3 34 8 32
ROW 8 4 6 9 43
ROW 9 5 8 10 5

I have 4 columns as above in one sheet, I want to match the numbers 1 to 10
and return the values in the column matching the number in another sheet as
below. Please note, the above format changes on a daily basis as it comes
from a web query e.g tomorrow there might only be 8 numbers, in which case 5
in A9 will move to C5 and A9 and C 9 will then be blank.

A B
ROW 5 1 65
2 100
3 34
4 6
5 8
6 56
7 41
8 32
9 43
Row14 10 5

The formula should be in B5 through B14 , is this possible? Thanks. Anthon.

10 formulas, wherever you want them:

=A5
=A6
=A7
=A8
=IF(A9="",C5,A9)
=IF(A9="",C6,C5)
=IF(A9="",C7,C6)
=IF(A9="",C8,C7)
=IF(A9="","",C8)
=IF(A9="","",C9)

You could do it with one formula, but it would be more complex and harder to
understand.

HTH,
Bernie
MS Excel MVP


"Anthon" wrote in message
...
A B C D
ROW 5 1 65 6 56
ROW 6 2 100 7 41
ROW 7 3 34 8 32
ROW 8 4 6 9 43
ROW 9 5 8 10 5

I have 4 columns as above in one sheet, I want to match the numbers 1 to
10
and return the values in the column matching the number in another sheet
as
below. Please note, the above format changes on a daily basis as it comes
from a web query e.g tomorrow there might only be 8 numbers, in which case
5
in A9 will move to C5 and A9 and C 9 will then be blank.

A B
ROW 5 1 65
2 100
3 34
4 6
5 8
6 56
7 41
8 32
9 43
Row14 10 5

The formula should be in B5 through B14 , is this possible? Thanks.
Anthon.

Anthon wrote:
A B C D
ROW 5 1 65 6 56
ROW 6 2 100 7 41
ROW 7 3 34 8 32
ROW 8 4 6 9 43
ROW 9 5 8 10 5

I have 4 columns as above in one sheet, I want to match the numbers 1 to 10
and return the values in the column matching the number in another sheet as
below. Please note, the above format changes on a daily basis as it comes
from a web query e.g tomorrow there might only be 8 numbers, in which case 5
in A9 will move to C5 and A9 and C 9 will then be blank.

A B
ROW 5 1 65
2 100
3 34
4 6
5 8
6 56
7 41
8 32
9 43
Row14 10 5

The formula should be in B5 through B14 , is this possible? Thanks. Anthon.



So are you just trying to look up the value in column B of the first
table and place it in the second table based on matching values in
columns A? (What is the significance of columns C & D in table 1?)

in table2!B5,
= VLOOKUP (A5, table1!$A:$A, 2, false)
fill down.

or, since you say values might be missing the the first table,

= If (ISNA(VLOOKUP (A5, table1!$A:$A, 2, false), "", VLOOKUP (A5,
table1!$A:$A, 2, false))

Since you say that the configuration can change on a daily basis, I would
think that the simplest approach would be to treat each pair of columns as a
separate datalist, and poll them individually.
There would be no harm in oversizing them to the possible maximum size,
which I believe from your example is two 5 X 2 arrays.
It shouldn't matter if the next day it's two 4 X 2 arrays.

With data imported into Sheet1, and your lookup list on Sheet2, as you
described,
Enter this formula in B5 of Sheet2:

=IF(ISNA(MATCH(A5,Sheet1!A$5:A$9,0)),IF(ISNA(MATCH (A5,Sheet1!C$5:C$9,0)),"No
Match",VLOOKUP(A5,Sheet1!C$5$9,2,0)),VLOOKUP(A5, Sheet1!A$5:B$9,2,0))

Copy down to B14.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Anthon" wrote in message
...
A B C D
ROW 5 1 65 6 56
ROW 6 2 100 7 41
ROW 7 3 34 8 32
ROW 8 4 6 9 43
ROW 9 5 8 10 5

I have 4 columns as above in one sheet, I want to match the numbers 1 to
10
and return the values in the column matching the number in another sheet
as
below. Please note, the above format changes on a daily basis as it comes
from a web query e.g tomorrow there might only be 8 numbers, in which case
5
in A9 will move to C5 and A9 and C 9 will then be blank.

A B
ROW 5 1 65
2 100
3 34
4 6
5 8
6 56
7 41
8 32
9 43
Row14 10 5

The formula should be in B5 through B14 , is this possible? Thanks.
Anthon.

=SUMPRODUCT((MOD(COLUMN(Sheet1!$A$5:$C$9),2)=1)*(S heet1!$A$5:$C$9=A5)*Sheet1!$B$5:$D$9)

copy down


"Anthon" wrote:

A B C D
ROW 5 1 65 6 56
ROW 6 2 100 7 41
ROW 7 3 34 8 32
ROW 8 4 6 9 43
ROW 9 5 8 10 5

I have 4 columns as above in one sheet, I want to match the numbers 1 to 10
and return the values in the column matching the number in another sheet as
below. Please note, the above format changes on a daily basis as it comes
from a web query e.g tomorrow there might only be 8 numbers, in which case 5
in A9 will move to C5 and A9 and C 9 will then be blank.

A B
ROW 5 1 65
2 100
3 34
4 6
5 8
6 56
7 41
8 32
9 43
Row14 10 5

The formula should be in B5 through B14 , is this possible? Thanks. Anthon.