Complex Formula using INDEX and Match needed

[OTPM]

Hi Fellow Forum Users

I am looking for a complex formula perhaps using Index and Match to achieve what I need.

Basically I have a range of data comprising 7 columns by 7 rows (A3 to G10).

I want to search for two values in columns A and B and then return the Maximum value found between columns C and G.

Here is the formula that I have just tried:

=VLOOKUP(A3&"-"&B3,'Collated time'!A3:H9,MAX('Collated time'!D3:H9),FALSE)



When I use the Function editor the correct value is displayed in the Result area. However in the cell that contains this formula I get #N/A.

Any help would be appreciated.

Kind regards
Tony

[macropod]

Hi Tony,

Have you tried it as an array formula?

[Pecoflyer]

It's often easier to help based on a sample sheet, being that I don't like typing unnecessarily.
Based on the attached you might be able to solve your problem

[OTPM]

Hi Both
Many thanks for the responses. I have just resolved my issue with help from a colleague.
Kind regards
Tony

[Pecoflyer]

Care to share your solution?

[OTPM]

Hi Pecoflyer
Under normal circumstances I would be happy to share the solution for the benefit of other Forum Users but due to the extreme sensitivity of the work I am doing I am unable to on this occassion. What I did was to slightly redesign the worksheet and use a series of more basic functions in order to achieve my desired end result.
Thanks
Tony