Data lookup with multiple conditions - array for text AND numbers?

I am currently using this formula to lookup corresponding data from sheet "Report", depending on if two conditions on Sheet 1 in column J (ID no. => text) and L (departure date => number) are met.

Sheet 1:

Please Login or Register  to view this content.

Sheet "Report"
ID no - departure date - confirmation number
1234 - 24.12.2010 - ABCD

wanted result: return "ABCD" as the correlative confirmation number.

If I change the confirmation number to an actual number, it works fine, but if it contains any letters, the formula returns "0". Any idea why?

Thank you very much,

A2k

MIN returns Numbers - if the values aren't numbers then....

It seems you're using XL2007 (or above) - first off avoid entire column references in your Arrays - the performance impact is significant.

It's not clear if you have multiple matches for the combination or not...

Please Login or Register  to view this content.

note: restricted ranges - modify but bear in mind the above point

Just out of curiousity, how would i need to adjust the formula to make this work under office 2003? I just noticed that I get a reminder regarding loss of functionality when saving this file as a xls via office 2010.

Thanks,
A2k

You need to replace IFERROR(formula,"") with IF(ISERROR(formula),"",formula)

In this instance - given text result and inefficiency of original I'd be inclined to use:

Please Login or Register  to view this content.

this way the INDEX is performed once only though this does assume valid results never exceed 255 chars in length.