Thread: Function to return a "true" if three values in an array match.

Hello, I need to figure out how to match three values on the "source info.xls" file attached to the "PFG FILE.xls" that is also attached. In column A in the "Source info.xls" file the formula will need to match the yellow column, then the green column, then the red column. Please keep in mind that the attached files are just a small subset of data. The "PFG File.XLS" is actually 150K rows long.

Thanks for your help!

If you have the volume of data you say you have then you should really be looking at means to keep the linked formula as efficient as possible.

On that basis I would suggest you do the following

Code:

PFG File.xls

P4: =$C4&"@"&$E4&"@"&$O4
copied down for all rows

Then

Code:

source info.xls

A3: =ISNUMBER(MATCH($C3&"@"&$H3&"@"&$F3,'[PFG FILE .xls]Sheet1'!$P$4:$P$150000,0))
copied down

NOTE: above formula assumes both files are open at the time of being added and that file is XL2007 (ie 150000 is a valid reference)

It would be even better if you could sort PFG File by C, E & O in that order of preference as you could then run a MATCH utilising Binary Search which would greatly reduce the amount of cells being processed.
That is the implication of your file but given there is only one store we can't be sure...

If the data is sorted by C & E & O then in theory you can conduct a MATCH using the default match_type setting of 1 rather than the exact match setting of 0

Code:

A3:
=INDEX('[PFG FILE .xls]Sheet1'!$P$4:$P$150000,MATCH($C3&"@"&$H3&"@"&$F3,'[PFG FILE .xls]Sheet1'!$P$4:$P$150000))=$C3&"@"&$H3&"@"&$F3
copied down

When MATCH uses it's default setting of 1/TRUE it uses Binary Search and searches a much smaller subset of data making it much much quicker.... esp. on large data sets such as this.

For a graphical representation of how Binary Search works see: http://www.mrexcel.com/forum/showpos...98&postcount=7

Of course it will not be conducting an exact match rather it will return the last value <= criteria value.
Given this fact we subsequently conduct an additional test to see if the value returned c/o of the binary search matches the criteria... this is our Boolean output.

Make sense ?