Index, Match

[Robbo1971]

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)?

 

[MintJulep]

It's because "M10" and "M3" are TEXT values, and thus getting treated as such by INDEX and MATCH.

Simple solution is to get rid of the "M" and with with the sizes only as numbers.

 

[melone]

Is it the optional parameters at the end of the Match function?

 

[IDS]

Two options:

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

http://www.interactiveds.com.au

 

[asixth]

Agree with IDS,

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)