# Index, Aggregate, Row - Removing Duplicate W/ Index Help

1. ## Index, Aggregate, Row - Removing Duplicate W/ Index Help

Hi team,

I'll try to keep this short, concise and simple. I've got two sheets of information, and I'm trying to leverage INDEX as a lookup function.

My goal is: When I enter a dollar amount into cell B, return the service related to that value dependent on the facility. If there are any duplicates, enter 2, 3, 4 etc to retrieve the next available service under that criteria.

Reason: I want to enter a dollar value because this is how our employees bill us their commissions - and they don't know how to use excel and write everything down. Also, using data validation is slow, even with combobox, for data entry.

My problem is: Facilities may have different services that are priced at the amount, at each facility.

A tiny sample of the issue is attached. I will need to extrapolate a solution over thousands of rows.

Problem Breakdown:

=INDEX(Pricebook!\$B\$2:\$B\$12,AGGREGATE(15,6,(ROW(Pricebook!\$A\$2:\$A\$12)-ROW(Pricebook!A2)+1)/(Pricebook!\$A\$2:\$A\$12=E2),C2))

In cell C2, if I change the value between 1 and 2, it works perfectly according to expectations. However, when I drop the formula down, the other returned values in column D are all wrong and do not meet expectations.

Any help is greatly appreciated,

Thanks,
Aaron

2. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

It would help if you could show the desired results. Try this formula

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

Dear,

Look again to the number.....are different.... 18,5 <> 18.5....try change....

4. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

*I hope I don't double post, I keep replying but nothing happens. Apologies if it does.

Thanks for the quick reply AlKey. I did as suggested but it did not quite work as planned. Taking in your feedback, here are my desired results in relative column D for the same row number as column C:

C2 = 1, returns 'Styling'
C3 = 2, returns 'Styling and Blow Dry'
C4 = 1, returns 'Cut & Blow Dry (no curl)'
C5 = 2, returns 'Cut & Shampoo'
C6 = 1, returns 'Haircut (both)'

Thanks again!
Aaron

5. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

Try

=INDEX(Pricebook!\$B\$2:\$B\$12,MATCH(A2&B2,Pricebook!\$C\$2:\$C\$12&Pricebook!\$D\$2:\$D\$12,0)+COUNTIFS(\$A\$2:\$A2,\$A\$2,\$B\$2:\$B2,\$B2)-1)

Enter with Ctrl+Shift+Enter

6. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

Here it is

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

After thought ..

put this in C2 and copy down

=COUNTIFS(\$A\$2:\$A2,\$A\$2,\$B\$2:\$B2,\$B2)

Formula in D2

=INDEX(Pricebook!\$B\$2:\$B\$12,MATCH(A2&B2,Pricebook!\$C\$2:\$C\$12&Pricebook!\$D\$2:\$D\$12,0)+C2-1)

Enter with Ctrl+Shift+Enter

8. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

Formula:
`Please Login or Register  to view this content.`

9. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

Nice! I am super happy, this helps big time. Either AlKey's or John's solutions work for anyone who is referencing in the future.

Real big thanks to the two of you, appreciate it.

10. ## Re: Index, Aggregate, Row - Removing Duplicate W/ Index Help

Alkey's is better as it is not an array-formula. Similarly for Czeslaw.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1