Luckily, I only had 49 stores that I needed to copy this down (there were
over 3,000 stores where it looked for a match). Copying this down 50 rows
was fast, just a couple of seconds).

To satisfy your curiousity I ran a test last night to see what happens with
more rows. I broke my tables up into similar sizes (1,564 and 1,566 stores
respectively). After dropping the formula down 1,566 rows, it took exactly 1
hour to finish calculating. There ended up being 310 stores with no matches.
I ran this on my Dell Latitude D620 laptop (Duo Core processors, 2GB RAM).

Thanks again for the help. Very cool formula........still don't know how it
works. :)

"David Hilberg" wrote:

Goofy11,

When I test across 2 worksheets, I have no problems. But perhaps in your
rearranging, the omission in the 2nd ABS function was corrected (see
previous post)?

I'll take your bow & redirect it to Harlan Grove et al, from whom half
of these techniques were poached!

BTW, how far down can the formula be copied before calculation time
becomes prohibitive?

- David


goofy11 wrote:
David,

Does this formula require that both tables (ranges) be on the same
worksheet? I'm asking because I rearranged my data so that they were on the
same worksheet, arranged how you had set up your test data and.........IT
WORKED!!! I don't understand how it works, but it does. Anyway, thanks a
bunch. I now bow to the array formula master. :)

"goofy11" wrote:

WHOA!!!! That is a beast of a formula! I thought I was fairly handy with
array formula's and I use Index/Match quite a bit, but this thing is a
monter. You kind of lose me on all the MMULT and TRANSPOSE stuff, but I
thought I'd be able to replicate it. Unfortunately when I dropped it into my
workbook, it is finding no results. I have table1 on Sheet1, and table2 on
Sheet2. I structured it so I'm using the same columns as you are below, but
I will add one point of clarification: when I proposed my question, the
example I showed had column B containing states. In actuality, column B will
either be populated with "yes" or "no". I would think it would work the same
either way, but since I'm experiencing difficulty, I thought I'd mention that
in case there's something I'm missing.

I am using Excel 2007 (thank goodness). Here is what the formula looks like:

=IFERROR(INDEX(Sheet2!$A$2:$A$3082,
MATCH(MIN(($B2=Sheet2!$B$2:$B$3082)*ABS($C2-Sheet2!$C$2:$C$3082)*(MMULT(--(Sheet2!$A$2:$A$3082<TRANSPOSE($E$1:$E1)),ROW($E$ 1:$E1)^0)=COUNTA($E$1:$E1))+999999999*(($B2<Sheet 2!$B$2:$B$3082)+(MMULT(--(Sheet2!$A$2:$A$3082<TRANSPOSE($E$1:$E1)),ROW($E$ 1:$E1)^0)<COUNTA($E$1:$E1)))),($B2=Sheet2!$B$2:$B $3082)*ABS(Sheet2!$C$2:$C$3082)*(MMULT(--(Sheet2!$A$2:$A$3082<TRANSPOSE($E$1:$E1)),ROW($E$ 1:$E1)^0)=COUNTA($E$1:$E1))+111111111*(($B2<Sheet 2!$B$2:$B$3082)+(MMULT(--(Sheet2!$A$2:$A$3082<TRANSPOSE($E$1:$E1)),ROW($E$ 1:$E1)^0)<COUNTA($E$1:$E1))),0)),"No Match")

Do you see anything that looks wrong?

"David Hilberg" wrote:

Correction #2. Formula should be longer still:

=IFERROR( INDEX($A$9:$A$12, MATCH(
MIN(
($B2=$B$9:$B$12)*ABS($C2-$C$9:$C$12)*
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) =COUNTA($E$1:$E1))+
999999999*(($B2<$B$9:$B$12)+
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) <COUNTA($E$1:$E1)))
),
($B2=$B$9:$B$12)*ABS($C2-$C$9:$C$12)*
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) =COUNTA($E$1:$E1))+
111111111*(($B2<$B$9:$B$12)+
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) <COUNTA($E$1:$E1))),
0)),
"No Match")


Notes
- The IFERROR function is newly available in Excel 2007. I don't see a
way around using it -- the alternative is a formula that is anyway too
large for Excel 2003.
- The two main blocks of 4 lines are the same except for the numbers
999999999 and 111111111. (Use any two #'s, not equal, that surpass the
largest Sales figure.)
- When all State candidates have been exhausted, "No Match" is displayed.
- See previous posts for other notes.


This formula works best of all -- I believe it covers all relevant cases
-- and will be my last effort, unless you have any questions.

- David


David Hilberg wrote:
Correction, formula should be longer:

=INDEX($A$9:$A$12,MATCH(
MIN(
($B2=$B$9:$B$12)*ABS($C2-$C$9:$C$12)*
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) =COUNTA($E$1:$E1))+

MAX($C$2:$C$5,$C$9:$C$12)*
(($B2<$B$9:$B$12)+
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) <COUNTA($E$1:$E1)))

),
($B2=$B$9:$B$12)*ABS($C2-$C$9:$C$12)*
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) =COUNTA($E$1:$E1))+

MAX($C$2:$C$5,$C$9:$C$12)*
(($B2<$B$9:$B$12)+
(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) <COUNTA($E$1:$E1))),

0))

This takes into account that a store in Table1 might have *exactly* the
same sales as a store from the same state in Table2.
Still enter with Ctrl+Shift+Enter.
Formula has form:

=INDEX(Stores2, MATCH( MIN( RemainingSalesDeltasAcrossCurrentState ),
RemainingSalesDeltasAcrossCurrentState, 0 ))

- David


David Hilberg wrote:
Hi Goofy11,

Since no-one has posted VBA yet, would you like a working formula? As
you requested, it requires an exact match for State, then returns the
store number representing the closest unused match for sales (whether
higher or lower).


=INDEX($A$9:$A$12,MATCH(
MIN(
($B2=$B$9:$B$12)*ABS($C2-$C$9:$C$12)
*(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) =COUNTA($E$1:$E1))

+MAX($C$2:$C$5,$C$9:$C$12)
*(($B2<$B$9:$B$12)
+(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) <COUNTA($E$1:$E1)))

),
($B2=$B$9:$B$12)*ABS($C2-$C$9:$C$12)
*(MMULT(--($A$9:$A$12<TRANSPOSE($E$1:$E1)),ROW($E$1:$E1)^0) =COUNTA($E$1:$E1)),

0))


It's on several lines to show that some expressions are similar or the
same.
It is an "array formula" that must be entered with Ctrl+Shift+Enter,
not just Enter, or you'll get #VALUE!
If a match cannot be made it returns #N/A.

In my test data,
Table1 = A2:C4
Table2 = B9:C12
Formula was entered in E2:E4.
In E1 (just above the formula) there must be a header (such as "Best
Unused Match").

I hope this is useful!

- David


goofy11 wrote:
I'm hoping someone can provide me with some vba to make quick work of
my task. I have 2 tables (call them table1 and table2) that are
setup like:

Store State Sales
1 MO 2,120
7 TX 3265
12 CA 4565
37 TX 3375

Both tables have these same fields, but each table has different
store numbers (no duplicates between the tables). I want to add a new
column to table1. For each store in table1, I want to return the
store number from table2 that is the closest match to itself based on
State and Sales. To elaborate further, I want to find a store that
is in the same state, and is closest in sales to itself. For the
sales parameter, it doesn't matter how high or low it has to
go.....it just needs to return the closest match.

The last condition, is that a result can only be used once. So if
store 7 found that store 325 was the closest match, and then later,
store 37 also found that 325 was the closest match for it, the code
would have to look for the next best match, and so on. Table 1 would
then look like:

Store State Sales Table2 match
1 MO 2,120 56
7 TX 3265 325
12 CA 4565 68
37 TX 3375 652

Any help is appreciated.