INDEX/MATCH formula looking in 2 columns and 1 row

Difficult to say without seeing the workbook but it looks like you're evaluating an array formula there which is probably resulting in the message you get.

Have you tried creating a helper column which concatenates the customer name, delivery point and rate, then using a simple vlookup (or INDEX MATCH combination) to find the relevant row using the helper column.

Hi,

Check the FAQ tab at the top of the forum. Go to the third pane 'Board FAQ' and choose the Reading and Posting messages then Attachments and Images.

Don't upload a screenshot, we require the workbook itself or at least a representative sample. Add some notes which describe (with reference to specific cells, ranges, or sheets) what you expect to happen.

Hi,

Don't know offhand but I think it's quite large.
All we need is a representative sample anyway so just delete non relevant rows. If it won't upload just remove some rows until it will.

Specifically what values do you expect to see in K18 & S18 and why?

A =VLOOKUP(J18,Customers!$C$1:$D$176,2,FALSE) in K18 will return the value "_1" but it's not clear if this is what you want.

M18 & O18 contain validation drop downs. Again it's not clear whether you are wanting to replace these with a formula or not. If you want the M18 'Macclesfield' value to be returned, how should the system determine that it should use the E126 cell on the customers sheet rather than E127, E128 etc. which are all also associated with Ben.

Similarly if O18 should be Stoke on Trent, what determines this? i.e. what's the relationship between Ben and Stoke on Trent.

My gut feeling is that the layout is not particularly usable for analysis and it may well be that a fundamental reorganisation might simplify things.

Thanks for your reply Richard, but I think you've misunderstood what I'm trying accomplish.

The value "_1" will already be entered on the page in the customer column of the calendar as this is the code that allows the data validation in 'From' and 'To' to choose the appropriate job list according to the customer.

When my company receives a job, me or one of my colleagues will firstly enter the customer, then choose the collection and delivery points and finally at what rate the job has been priced at; this all works exactly how I want it to (accept that the drop down values have to be chose, it doesn't accept it if you type them for some reason). All I need is an equation that finds the correct price, at the specified rate, for the specified destination, for the specified customer.

K18 has an arrow pointing to J18 which is just showing you where a customer gets selected. S18 is the price and this is what I want to calculate, based on the values I've mentioned above, from the grid of information that is on the Customers worksheet.

As I mentioned above, M18 and O18 both work perfectly (near enough) and they have only been filled in and pointed out as I want them to be involved in the parameters of the formula held within S18.

The formula that I posted earlier was based on, as I mentioned, one that I used in our stock login sheet that firstly looks for the name of the stock item, then the date specified and finally what storage facility it is being stored in, and it works beautifully. The only difference to the formula I'm trying to put together now is that I need it to not only search down columns for the first row that matches both the customer do and the destination (this information being held within columns D and F in the Customers worksheet) but search row 1 for the matching rate and equal the value in the single cell that matches all of these criteria.

Let me know if you now understand what I'm trying to achieve a bit better and whether you need more information.

Many thanks,

Ben