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

Hi Tony,

Have you tried it as an array formula?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

Attached Files

Max example.xlsx (11.6 KB, 9 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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

Care to share your solution?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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