Lookup & match
 

I need help with a formula that will return the least column No.
when the contents of an array in the same row match
the contents of a lookup cell.
e.g. the lookup cell is Cell(J1) with content 3
Range("A3:H3") has array value : (4, 3, 9, 3, 3, 5, 7, 8)
Cell(J3) looks up Cell(J1) and find B3, D3 & E3 that match.
2, 4 & 5 are the column Nos of B3, D3 & E3 respectively.
Therefore Cell(G3) should returns 2 (the least value among 2, 4 & 5)
Appreciated!

Daniel

In cell J3 type

=MATCH(J1,A3:H3,0)

The '0' means match type 0. This will select the first value that is equal
to the lookup value. This means that because it reads the array A3:H3
starting with column A that it will always return the least column number.

Hope this helps


Alex

"daniel chen" wrote:

I need help with a formula that will return the least column No.
when the contents of an array in the same row match
the contents of a lookup cell.
e.g. the lookup cell is Cell(J1) with content 3
Range("A3:H3") has array value : (4, 3, 9, 3, 3, 5, 7, 8)
Cell(J3) looks up Cell(J1) and find B3, D3 & E3 that match.
2, 4 & 5 are the column Nos of B3, D3 & E3 respectively.
Therefore Cell(G3) should returns 2 (the least value among 2, 4 & 5)
Appreciated!

Hi, Alex
Thank you very much.
I overlooked the order condition 0.

"Alex" wrote in message
...
Daniel

In cell J3 type

=MATCH(J1,A3:H3,0)

The '0' means match type 0. This will select the first value that is equal
to the lookup value. This means that because it reads the array A3:H3
starting with column A that it will always return the least column number.

Hope this helps


Alex

"daniel chen" wrote:

I need help with a formula that will return the least column No.
when the contents of an array in the same row match
the contents of a lookup cell.
e.g. the lookup cell is Cell(J1) with content 3
Range("A3:H3") has array value : (4, 3, 9, 3, 3, 5, 7, 8)
Cell(J3) looks up Cell(J1) and find B3, D3 & E3 that match.
2, 4 & 5 are the column Nos of B3, D3 & E3 respectively.
Therefore Cell(G3) should returns 2 (the least value among 2, 4 & 5)
Appreciated!