match a value found with that on another colum

Posted by Dashe on August 03, 2001 3:32 AM

below is an example of a chart

01101 | 0 | 0 | 1 | 0 |
01102 | 0 | 0 | 0 | 2 |
01103 | 0 | 4 | 0 | 0 |
01104 | 0 | 0 | 3 | 0 |

1 | 01101
2 | 01102
3 | 01104
4 | 01103

What i am trying to do is on the bottom table where it has the cell with the number 1 in it i want to put a formula into the next call that will search the table above and fint the number 1 and return the value in the first colum. As you can see the number 3 was found on the row 01104. I cant seem to figure out a formula to do this. Any ideas would be apriciated

Thanks
Dashe

Posted by Aladin Akyurek on August 03, 2001 2:30 PM

Dashe,

Here a solution that, I admit, I hesitate to post.

I entered the table (your "chart")

{"01101",0,0,1,0;"01102",0,0,0,2;"01103",0,4,0,0;"01104",0,0,3,0}

into the range A2:E5.

Enter the target values (the 1st column of your smaller table)

{1;2;3;4}

into the range G2:G5.

In I1 enter: =ADDRESS(ROW(2:2),COLUMN(B1))&":"&ADDRESS(COUNTA($A:$A)+1,COLUMN(B1)) [ copy this to J1:L1.

In I2 enter: =IF(ISNUMBER(MATCH($G2,INDIRECT(I$1),0)),MATCH($G2,INDIRECT(I$1),0),"") [ copy this first across then down, as far as needed ]

In H2 enter: =INDEX($A$2:$A$5,SUM(I2:L2)) [ copy this down to H3:H5 ]

In G2:H5 you'll see

{1,"01101";2,"01102";3,"01104";4,"01103"}

as result.

Note. If interested in receiving the workbook behind the system above, just drop me a line.

Aladin