index match

  • Thread starter Thread starter Anthon
  • Start date Start date
A

Anthon

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
 
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:D$9,2,0)),VLOOKUP(A5,Sheet1!A$5:B$9,2,0))

Copy down to B14.
 
=SUMPRODUCT((MOD(COLUMN(Sheet1!$A$5:$C$9),2)=1)*(Sheet1!$A$5:$C$9=A5)*Sheet1!$B$5:$D$9)

copy down
 
Back
Top