How to use the function match?
 

Does anyone have any suggestions on how to use the function match?
For example, I would like to match the value in cell A1 with a list of cells
in different location.
=match(A1,[C2,F56,H8,J11],0), but the syntax is wrong.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to use the function match?
 

.. match the value in cell A1 with a list of cells in different location.
You need to link all of those different location cells into a single
contiguous col or row range somewhere, then you can apply MATCH in the normal
way to match the lookup value against that contiguous range, eg:
=match(A1,ContiguousRange,0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:
Does anyone have any suggestions on how to use the function match?
For example, I would like to match the value in cell A1 with a list of cells
in different location.
=match(A1,[C2,F56,H8,J11],0), but the syntax is wrong.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to use the function match?
 

MATCH returns the relative position of the lookup_value in an array. Based
on your sample this could be done with the below formula.

Assuming the values are numeric. If the values are TEXT replace the N
function with the T function. If the values are of mixed types this won't
work!

=SUMPRODUCT(MATCH(A1,N(INDIRECT({"C2","F56","H8"," J11"})),0))

However, your references are all in different columns so the match order is
linear left to right. How would you want to handle multiple refs in the same
column? If you wanted to maintain a linear pattern from left to right you'd
have to list the refs by column then row:

C2, C27, C44, F56, H8, H22, J11, J12

You'd probably be better off using Max's suggestion.


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to use the function match?
For example, I would like to match the value in cell A1 with a list of
cells
in different location.
=match(A1,[C2,F56,H8,J11],0), but the syntax is wrong.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric