×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Index, Match

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

RE: Index, Match

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.

RE: Index, Match

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

RE: Index, Match

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
www.interactiveds.com.au
 

RE: Index, Match

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources