Formula for Bid Type with multiple criteria within data range

[kikijean]

Hello,
I'm having trouble explaining what I need in a brief title. I have attached a sample workbook with the details below of what I need the formula to do.

These are bids in an auction. A customer can submit a 'Straight' bid at the next increment or a 'Max' bid in which the system bids for them up to their Max value.

I need a formula in Column H to identify the type of bid as either 'Straight', 'Max' or 'Auto'

'Straight' should apply when the Bid (column E) is equal to the Ceiling (column F) and is NOT an auto bid

'Max' should apply when the Ceiling (column F) is greater than the Bid (column E) AND is the [I]first bid with a Difference (column G) greater than $0 AND by that Bidder (column C) AND for that Lot (column A)

'Auto' should apply subsequent to a Max Bid - when the Bid (column E) is equal to or lower than the Ceiling (column F) of the initial bid by that Bidder (column C) AND for that Lot (column A) (which should be labeled 'Max').

I think its easier to understand with the example data.
Looking at the bids for Lot 8:
In row 12 John Smith entered a STRAIGHT Bid of $6000.
In row 13, Jane Smith entered a MAX Bid of $8500. The system bid $6500 which is the next increment after 6000.
In row 14, John Smith entered another STRAIGHT Bid of $7000
In row 15, the system AUTO bid on behalf of Jane Smith at the next increment of $7500, because it is less than or equal to the Ceiling of $8500 that she entered as her Max Bid in row 13.
In row 16, John Smith entered another STRAIGHT Bid of $8000
In row 17, the system AUTO bid on behalf of Jane Smith at the next increment of $8500 because it is less than or equal to the Ceiling of $8500 that she entered as her Max Bid in row 13.
In row 18, John Smith entered another STRAIGHT Bid of $9000
In row 19, Jane Smith entered a new MAX Bid of $9500

The formula to determine Max and Auto Types must also be specific to the bids within that Lot (column A)

Hope this makes sense, please ask questions if not.

[josephteh]

Cell H2 =IF(E2=F2,"Straight",IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2,G$2:G2,">0")=1,"Max","Auto"))

[kikijean]

Thank you, this is very close but I do see a couple issues. I have applied the formula to the test data in the attachment and highlighted where it is returning the incorrect type.
First, it looks like it does not recognize an 'Auto' bid when the 'Bid' amount equals the 'Ceiling' - refer row 17
Second, it does not recognize a new 'Max' bid by the same Bidder on the same Lot - refer row 19
It is possible to have the formula recognize these?

Thank you,
Katie

[josephteh]

If row 16 is a 'Straight' bid when Bid equals to Ceiling, why is it different for row 17?

There is already a Max bid on row 13, as defined in your 1st post Max should apply when Ceiling > Bid & is the 1st bid > $0.