Correcting a formula INDEX + MATCH

[hlc]

The formula I am working with is below - it should return a value based on the criteria selected from drop down menus in A12 & B12 - it works correctly for every menu item except for one. I realized that I missed one position this morning and added it to the reference list. I used Data Validation to build the drop down lists in A12 and B12. I used CONCAT to merge the fields in the reference spreadsheet to build out the factor combinations that would result in an amount. For example.

In A12 the user selects Quality Technician
In B12 the user selects Faro Arm Operations

This combination results in the spreadsheet auto populating the increase amount as .90.

This is working like a charm with the exception of the one position that I added today. It is included in the CONCAT references but I can't figure out why it will not return the amount like the other positions. If I could figure out how to upload the workbook, I would but I am not sure if it's user error or a firewall problem.


=INDEX(Rates!$D$1:$D$61,MATCH(A12&B12,INDEX(Rates!$B$1:$B$61&Rates!$C$1:$C$61,FALSE),0))

[Glenn Kennedy]

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Pete_UK]

The Paperclip icon does not work on this forum, but you can still attach a sample workbook to any of your posts (and your description above doesn't make much sense without it).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

[hlc]

Thank you for the instructions - I have attached the document that I am struggling with.

[Pete_UK]

Your formula refers to a Rates sheet, but I can't see a sheet of that name in your attachment.

Pete

[Glenn Kennedy]

So... what doesn't work?? Be precise!!

[Glenn Kennedy]

Your formula refers to a Rates sheet, but I can't see a sheet of that name in your attachment.

Pete

It's hidden (helpfully!!).

[Pete_UK]

Thanks Glenn,

I wondered why it returned #N/A rather than #REF.

I don't think I'll bother with it, though, if it means doing a lot of looking around.

Pete

[hlc]

My apologies - hopefully you can see the rate reference now. Specifically, the calculations are working with the exception of the "Shipping/Receiving Clerk" position. It should return a value for the following skill blocks; new equipment inspection, computer use, spare parts processing, receiving (incl inspection), and export shipments. It isn't returning any values at all.

[Glenn Kennedy]

This is based on your post at No 1. I have not looked at the later attachment.

Green cells. Data Validation , list =Valid

Formula for DV:
=INDEX(Rates!$C:$C,AGGREGATE(15,6,ROW(Rates!$B$2:$B$61)/(Rates!$B$2:$B$61='STATUS CHANGE FORM'!A12),1)):INDEX(Rates!$C:$C,AGGREGATE(14,6,ROW(Rates!$B$2:$B$61)/(Rates!$B$2:$B$61='STATUS CHANGE FORM'!A12),1))

This selects only the possible valid answers. Your price-finding formula seems OK.

Forget about concatenation on the raw data sheet.

[Fluff13]

There is no "Shipping/Receiving Clerk" in the dropdown because you haven't added it to the list used by the data validation

[hlc]

Glen - thank you, that is amazing. I can only hope to become an excel guru.. I am still in full push learning mode

[Glenn Kennedy]

As are we all!!

You're welcome. The only limitation of this approach is that you have to sort the list to make sure that all blocka appear beside each other. They do currently. But if you add values, just re-sort by column B and then by column C.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.