Lookup two Values in Table

State Product Bonus Rate
NY NY Plus 5%
NJ Xtra 5%
NJ Preferred Xtra 5%
NJ Xtra 5%
NJ Preferred Xtra 5%
AK Xtra 5%
AK Preferred Xtra 5%
CT Xtra 5%
CT Preferred Xtra 5%
DE Xtra 5%
DE Preferred Xtra 5%
FL Xtra 5%
FL Preferred Xtra 5%
MN Xtra 5%
MN Preferred Xtra 5%
OR Xtra 5%
OR Preferred Xtra 5%
PA Xtra 5%
PA Preferred Xtra 5%
TX Xtra 5%
TX Preferred Xtra 5%
UT Xtra 5%
UT Preferred Xtra 5%
WA Xtra 5%
WA Preferred Xtra 5%
Other Xtra 8%
Other Preferred Xtra 8%

Well there are not that many combinations. Basically there are two rates 5% and 8%. The examples above are the states and products that should return the 5% and all others not contained in this list should return 8%.

I'm trying to populate column C on the calc tab with a matching rate from the Table tab based on State and product.

I have a better example. I did want to calculate one more item. If the product is not in the table I want the bonus rate to = 0%.

So for example:

C2 on Calc Sheet bonus rate would be 0 because the product is not listed on the table tabl

C417 would be 5 % because MultiChoice Income Plus in CT is listed at 5%.


C398 Would be 8% becaue MultiChoice Income Plus in IA is not on the table as a match on state and product but does match the criteria for C48. it's a product match but not a state match.

I will check this when I get back to work tomorrow. I really appreciate your help with this. This is the last info that I am not able to figure out. MUCH appreciated.

Originally Posted by NBVC

Does this work?

Please Login or Register  to view this content.

copied down.

This worked fantastically. You making me look like the star in the office today. Much thanks I really appreciate your help.

I have a VERY similar issue to this that only requires the return of yes or no. I've written up what I believe to be close but then again any help on where this is wrong would be greatly appreciated.

=IF(SUMPRODUCT(--(productMVA=L2),--(StateMVA=K2),MVAResult),SUMPRODUCT(--(productMVA=L2),--(StateMVA=K2),MVAResult),IF(COUNTIF(StateMVA,F7),No))

I'm trying to look to a table for a state and product and if it's there return yes and if not no.

The state and product have to be in the combo listed so it would have to be a True True for column A and B.

I've attached my work. Any constructive feedback is always welcome.

YOUR Amazing. Thanks Again.

Allright last time on here today, You've shown me how to select based on criteria and then give one of three responses, in the second example you explained how to give a simple yes answer based on a True True in a two column table.


What I need to do here is return a Yes or no based on three columns:


On the Calculation Sheet Columns, AH and AI are the criteria I'm using. if those two values such as MultiChoice Income 5 in IA are on the MVA Table in Column C for product and D for State and Have a value of MVA in column E ASCODE I would like a result of Yes brought back to cell AJ2 on the Calculation sheet. If that combo does not have MVA in Column E then No.

I'm not able to upload the file. Could I send it to your email? I don't see a place for attachements either for pm.