Pulling rate from a table using two variables using SUMPRODUCT, INDEX, MATCH

[uffda1]

I need to pull the rate using a table with multiple effective dates (column b in the Rate attachments) and multiple names. On the attached images I am able to pull the data if there is only one name in the source data, but my formula does not work when I have multiple names.

Formula:
=SUMPRODUCT((Rate2!C:C*1)*(Rate2!A:A=INDEX(Rate2!A:A,MATCH(A2,Rate2!A:A,0)))*(Rate2!B:B=INDEX(Rate2!B:B,MATCH(D2,Rate2!B:B,1))))

1 Name.PNG
Rate2.PNG
2 Names.PNG
Rate.PNG

This is the first time I've tried using these functions and any guidance would be very much appreciated!

Thank you.

[sanram]

You are matching the value of column D with the value of Column B. But B is Rate and D is Date. That's why your formula isn't working.

This may be

Please Login or Register  to view this content.

But I am not sure without seeing your file.

[jason.b75]

Security software blocks most images embedded in the forum, you can see them if you post them because the original is still in your browser cache. Everyone else will see one of those little 'Image cannot be displayed' icons!

Please attach a sample file containing relevant fictional data to illustrate your problem.

As you have a partially working formula, enter that into the sheet as well to show the results it returns.

In the next column, type in the results that you actually want so that we can clearly see the differences.

Important bit! Please check your 'Expected results' column carefully. (many people don't check, it is unbelievable how often we see a sample where the expected result of 1+1 is 3)

[uffda1]

Thanks for the tips! I attached the file to the original post. The formula is in column B.

[jason.b75]

Based on your sample file, try this formula

=LOOKUP(2,1/(($F$2:$F$17=A2)*($G$2:$G$17<=D2)),$H$2:$H$17)

Sumproduct can be a powerful tool, it could possibly be used for what you need, but the lookup method is much simpler.

[uffda1]

It looks like this works!!! Thank you so much! I'll apply to my larger dataset today and do some testing, but it looks good so far. I don't understand the "2,1/" logic so have some learning to do.

Thank you!

[jason.b75]

The array elements return TRUE or FALSE for each row in the table

(($F$2:$F$17=A2)*($G$2:$G$17<=D2))

TRUE * TRUE = 1, anything else = 0, so you only get 1 in the rows that meet both criteria.

Then comes the division part, 1/(array) 1/1 is still 1, 1/0 will result in a #DIV/0 error for the rows that don't meet the criteria.

LOOKUP uses an approximate match, so it finds the last entry less than or equal to the criteria of 2, i.e. the last row that the array returns 1, which is the most recent date that meets both criteria.

Hope that makes sense.

[uffda1]

Thank you for explaining. It all makes sense to me now except the 2. Does the 2 mean the formula is comparing the last 2 results?

[jason.b75]

No, it's just looking for the last occurrence of 1 in the array, 2 is greater than 1, so it will always find the last result, regardless of how many there are.

[uffda1]

Ok, thank you so much! I appreciate your time!