Thread: Lookup or match /index

Having found a solution to my problem about archery handicaps through the forum I now have a further and more complex (I think) issue.

I am trying to use the match and index functions but it is dawning on me that while I can use them (now) in simple cases I don't fully understand INDEX, which has two forms, the reference and array and an area function I haven't quite grasped.

In the spreadsheet I want to enter Bow, Category, Round and Score and derive the Classification.

I've been trying to locate the table position using match statements and can find the area (or mini table) I want but can't seem to use INDEX to define the array and pick out the classification which equates to the score.

Any help appreciated.

David

Check the attachment, I used an auxiliary cells (highlighted in red).

Regards

It returns the wrong clasification when the score match exactly with one of the ranges. If you type 884 in cell K10, it returns 2nd Class instead of 1st Class.

Regards.

Thanks for the replies.

The complexity and sophistication of the answers demonstrates to me why I was struggling. Thanks to both of you. Thhanks also for getting to grips with the wonderful world of archery.

I haven't had chance to look in any detail to try and work out what you've done however they both look really good.

I had a problem with the sailpaty solution. When I changed the first row of the block of solutions by making gents ladies it turned classification,f-row and l-rowto #num! and then when I changed it back to gents it remained like that. I don't understand it.

I liked the WHER solution too since the drop down is similar to the other bits of the sheet I have designed. If I have to make modifications to the table to make it work then I'd do it though its a bit of work. Shame about the problem when the score matches the classification threshold but it must be nearly there. I hope yu can put it right.

Regards

David

Originally Posted by davidwp

I had a problem with the sailpaty solution. When I changed the first row of the block of solutions by making gents ladies it turned classification,f-row and l-rowto #num! and then when I changed it back to gents it remained like that. I don't understand it.

Sorry I can't duplicate your error, the ladies and gents should be typed exactly the same as they are in column B. Please attach the file with the error to see if I can fix the issue.

Regards

If the source data is like the attachment in post #3, this maybe works.

=INDEX(C:C,MATCH(1,INDEX((A:A=H10)*(B:B=I10)*(INDEX(1:65536,,MATCH(J10,2:2,0))<=K10),0),0))

Regards

sailepaty,

Good call, both approaches now incorporated in attachment in post #3.

Thanks guys. Very elegant.

I need to disappear and try and understand it, then adapt it to the full tables.

By the way. And it must b eobvious, how do I add a 'drop down' arrow to the bow type enttry box. I usually use drop down lists which return a number to a linked cell, but this seems better?

Sailepaty the attached file shows what happens when I put in ladies (paste value from column b)

I don't get the error, but it was an issue in the formula of the FRow. I hope it works for you now. For the "drop down" option check the Excel help or in the forum for Data Vakidation.

Regards

Guys - I hope you are still there.

I said I would go away and get my head round your previous solutions - however I can't get them to work on the actual situation I'm working on, and its driving me round the bend. My stupidity not any fault of yours!

The actual data and layout is attached ( I hadn't scanned it in before), and is slightly different from my earlier example, being on a separate page from the 'input sheet', and some categories having 5 classifications and some 6.

I have filled in the blanks in the tables as suggested in previous answers.

I'm hoping you can help again. With luck it should be just a tweak of the previous methods - but I just can't see it myself.

Thanks in anticipation.

I found this problem interesting enough to reformat your data. See attached - Classification Sheet. Your question is a perfect fit for Data Tables and Advanced Filters.

To use this you would type in your criteria in Row 2 and click the button. Even if you don't use the filtering you still may be able to do lookup functions easier with the data reformatted.

Thanks again Sailepaty.

Again I'll go away and try to figure it out.

Marvin.

I didn't get the attachment so perhaps you can post it. ts a different approach which I hadn't thought of.

David