Single Search Output

[maacmaac]

I have a spreadsheet (called “output”) with a single list of unique account numbers in column A. In columns B, C, D (column B = Invoice, column C = Sales, column D = class), I need to add information from a data file based upon certain search criteria which is as follows:

For each account number I need to list an invoice number and the corresponding sales amount and class. I need to list the invoice with the “highest” sales amount. There is only one catch to the issue I am encountering and this is I need to first look for the highest amount in Class A sales, if no Class A sales, then look for the highest in Class B sales….and then Class C….and then Class D. If there are no sales in Class A, B, C, or D, then return a value of “no match found”.

I have attached a spreadsheet that includes a sample of desired output for clarification. Thank you in advance for any comments.

[teylyn]

Hi,

possible approach involving sorting the data sheet:

Sort data sheet by Account ascending, Class ascending, Sales descending.

Then on Output sheet:

B2

Please Login or Register  to view this content.

C2 =IF(B2="no match found","",VLOOKUP($A2,data!$A$2:$D$70,3,FALSE))
D2 =IF(B2="no match found","",VLOOKUP($A2,data!$A$2:$D$70,4,FALSE))

copy down. see attached

hth

[maacmaac]

Formulas are working as needed. Thank you.