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
Bookmarks