Using a list box to perform lookups on a table

**davidwp** · 07-10-2010, 08:37 PM

In archery there are a number of different rounds shot (about 60).

A particular score on a round results in a handicap rating.

I have been trying to enter the score and the round shot (from a list box) and use a VLOOKUP to establish the handicap from a table.

I can't figure out how to do it! How to make the table array a variable which can change according to the selection in the list box

I have achieved the result I need in other ways on a small scale by having large nested IF statements based on the round name but there is a point when the formula gets too big.

Can anyone help? Or can anyone come up with a macro to do it?

I have attached a very simple example.

Thanks

**Marcol** · 07-10-2010, 09:15 PM

Perhaps
In B12

=IF(C4="Round A",LOOKUP(B4,E3:E7,H3:H7),IF(C4="Round B",LOOKUP(B4,F3:F7,H3:H7),LOOKUP(B4,G3:G7,H3:H7)))

I've taken out the listbox and added a validation list to C4.
Select from the drop-down in C4 to see the result in B12

Hope this helps

**davidwp** · 07-10-2010, 09:37 PM

Thanks Marco.

What you propose works - which is good.

The only issue I have is that there are in excess of 60 different archery rounds so this would become a rather large formula. In addition I didn't mention, because the principle will be the same that each handicap rating for each round derives an "allowance" which will involve another similar formula, and yet more a classification rating which will require a third.

There has to be a more elegant solution but I can't get anything to work.

**Teethless mama** · 07-10-2010, 10:05 PM

>>There has to be a more elegant solution but I can't get anything to work

Try this elegant solution:

=INDEX(H3:H7,MATCH(B4,INDEX($E$3:$G$7,,MATCH(C4,E2:G2,))))

Adjust to suit

**Marcol** · 07-10-2010, 10:47 PM

My apologies for the rather rustic approach to the part of the problem you chose to reveal.

**davidwp** · 07-11-2010, 04:50 AM

Marco

Ooops. Sorry if I sounded ungrateful I really wasn't. I should have explained the problem better but I didn't want to get bogged down in the detail and the huge tables involved.

No excuse but I had got to the stage where I couldn't see the wood for the trees at the end of a long day. I value the efforts of you and others to help.

Thanks again

David

**Marcol** · 07-11-2010, 05:11 AM

If you put together a complete list of your handycap system and the allowances, then perhaps we can reduce the size of this table and then provide a more comprehensive solution.

Cheers.

**davidwp** · 07-11-2010, 08:35 PM

Teethless Mama your solution worked perfectly for me.

I have now done the first part of what I needed to do. From now on I am going to be a big fan of the INDEX function and the MATCH FUNCTION.

I'm going to take a break and try to really understand and work with the spreadsheet, then I'm going to import the next couple of tables that I need to lookup data from.

I may need help again!

Thank you very much.

David