INDEX MATCH where one look up value is a date

Hi,

I'm attempting an Index Match to look up values in two seperate columns and return a value from a third column.

This is already working properly in my spreadsheet. However, one of the values to look up is a date in the format 31/01/2011 - which I think is the reason it is now returning an error.

Do I need to insert a prefix for this value... like MONTH(), DATE() or something?

This is the formula I have got so far:

=INDEX('Contracts Full Listing'!A:O,MATCH(Forecast!B5&K5,'Contracts Full Listing'!B2:B950&'Contracts Full Listing'!K2:K950,0))

I think you'd be best off posting a dummy workbook showing what you're trying to do, but from what you're saying I don't think INDEX/MATCH is the right combination of formulae.

You certainly can't use MATCH over two columns at the same time, which is what you appear to be doing with the above formula.

Hi pinkshirt

try it

=INDEX('Contracts Full Listing'!A:O,MATCH(Forecast!B5&K5,index('Contracts Full Listing'!B2:B950&'Contracts Full Listing'!K2:K950,0),0),1)


kindly note that when look up in multiple column, index the lookup range. further, when look up in multiple columns as A:O in above sheet, the column # is necessary which was missin in your formula

either enter a column # or use formula to pick relevent column # in the above formula

Thanks for your help.

Azam,

Your suggestion is much closer then mine was - however, it doesn't seem to be 'matching' the K5 value properly...

Thanks!

pinkshirt

Hi pinkshirt

Kindly upload a sample workbook

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

Azam,

Attached is a sample workbook.

What I am trying to achieve is the 'Price' on the forecast sheet where the Key and Date match on the Contracts Full Listing sheet.

Thanks!

If you have only one price per key per date, you could use:

=SUMPRODUCT('Contracts Full Listing'!B2:B13*('Contracts Full Listing'!A2:A13=Forecast!A2)*('Contracts Full Listing'!C2:C13=Forecast!C2))

Thanks for your help brokenbiscuits but this just returns 0.00

Brokenbiscuits, I amended your formula slightly to have --(array) in it and it works beautifully! Thanks a bunch!