Index, Match
Index, Match
(OP)
Excel 2002
I have a table of threads: M3 down to M10
I am using Index, Match to find specific information from the table relating to the thread for a calculation depending which thread I pick using data validation.
If I have the threads listed in the table from M3 down to M10, the selection does'nt work... if I put the M10 above the M3, it works. I would prefer to have the M10 below M8!
Is there another calculation in Excel where it looks at a table in the order of the thread, rather than numeric order (I currently am having to put M10 above M3)?
I have a table of threads: M3 down to M10
I am using Index, Match to find specific information from the table relating to the thread for a calculation depending which thread I pick using data validation.
If I have the threads listed in the table from M3 down to M10, the selection does'nt work... if I put the M10 above the M3, it works. I would prefer to have the M10 below M8!
Is there another calculation in Excel where it looks at a table in the order of the thread, rather than numeric order (I currently am having to put M10 above M3)?





RE: Index, Match
Simple solution is to get rid of the "M" and with with the sizes only as numbers.
RE: Index, Match
RE: Index, Match
Insert a 0 after the lookup range in the MATCH() function. This will do an "exact match" and doesn't require the list to be in order.
Or insert a 0 before the numbers less than 10 in the list, e.g. M03, M04 etc
Doug Jenkins
Interactive Design Services
www.interactiveds.com.au
RE: Index, Match
The simpliest way to go about this problem is to add 0 as your final input variable using MATCH().
For your example, say you have the threads M3 to M10 in the vector A1:A8, and you want to extract data for your M10 thread using INDEX(), to determine the row, your match function should look like:
=MATCH("M10",A1:A8,0)
This should return the value 8.
If your input only reads:
=MATCH("M10",A1:A8)
Then the value returned will be #N/A.
Just say you want to extract the data stored in B1:B8 for the M10 thread, your cell should read:
=INDEX(A1:B8,MATCH("M10",A1:A8,0),2)