Lookup & match

[daniel chen]

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!

[Alex]

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!
>
>
>

[daniel chen]

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

"Alex" <[email protected]> wrote in message
news:[email protected]...
> 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!
>>
>>
>>