Solved: matching that finds the greatest/least amount

[Danny]

I have 2 sheets,
SHEET1 "Balance":
has a list of company names in column B and balances for different sales in column D.

SHEET2 "Payments":
has a list of company names in column H and payments for different sales in column K.

I am wanting to do a lookup to pull the payment amounts from sheet2 into sheet1 Column E.

BUT......
Companies may appear on both sheets multiple times (usually not more than 3). Meaning that they have mutiple balance amounts and multiple payment amounts, and payments often do not equal the full balance amount (but they are close).
I am trying to write a code that would match the highest payment amount with the highest balance amount, and so on. A VLOOKUP with 2 criterion can knock out a portion but not much. (wouldn't a unique ID be great !!!!! )

Anyway, i am starting to ramble, hope this makes some sense.
The attachment has a third sheet showing the ideal outcome, but any help or ideas would be appretiated. There is a macro in module 1 called calcPMT I have been trying to use (if anyone needs a good laugh).

[Simon Lloyd]

If you only want the largest amount then simply use the max function in column E of your sheet like this:
=MAX(Sheet1!A1:A18)

[Bob Phillips]

Use these array formulae

=IF(ISERROR(LARGE(IF(Payments!$H$1:$H$20=Balances!B14,Payments!$K$1:$K$20), 1)),0,
LARGE(IF(Payments!$H$1:$H$20=Balances!B14,Payments!$K$1:$K$20),1))

=IF(ISERROR(LARGE(IF(Payments!$H$1:$H$20=Balances!B14,Payments!$K$1:$K$20), 2)),0,
LARGE(IF(Payments!$H$1:$H$20=Balances!B14,Payments!$K$1:$K$20),2))

=IF(ISERROR(LARGE(IF(Payments!$H$1:$H$20=Balances!B14,Payments!$K$1:$K$20), 3)),0,
LARGE(IF(Payments!$H$1:$H$20=Balances!B14,Payments!$K$1:$K$20),3))

[Danny]

xld,
Sorry it has taken me so long to respond.

Do i need to enter the formulas above in so that the formula would correspond with the value amount on the "balance" sheet, (the top formula goes with the greatest value on the "balance" sheet, and so on) or is there an easier way to enter these in that i am missing?
I have played with the formula to where it works but it is kinda a mess:

=IF(ISERROR(LARGE(IF(Payments!$H$1:$H$20=Balances!B20,Payments!$K$1:$K$20), IF(D20=MIN(IF($B$3:$B$20=B20,$D$3:$D$20,"")),COUNTIF(B:B,B20),IF(D20=MAX(IF ($B$3:$B$20=B20,$D$3:$D$20,"")),1,2)))),0,
LARGE(IF(Payments!$H$1:$H$20=Balances!B20,Payments!$K$1:$K$20),IF(D20=MIN(I F($B$3:$B$20=B20,$D$3:$D$20,"")),COUNTIF(B:B,B20),IF(D20=MAX(IF($B$3:$B$20= B20,$D$3:$D$20,"")),1,2))))