Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |