• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Returning a Value based on two Criteria

Hi YPJ -

If I've understood your question correctly, I've just extended your lookup to have the prices in it ... That will be simpler than having a two way lookup elsewhere

If I've not correctly divined your problem, I think adding further permutations of the plan configurations to your "report" will solve it ... and you can use several different techniques to pick out multiple criteria from the Table - my favorite would be SUMPRODUCT, but there are other equally effective solutions ...
 

Attachments

Hello DE,

Thank you for replying. This doesn't work for me because the report section includes 200 employees, the health/dental cost may change, and the information is used for another workbook.

Is there another way? Similar to how I was able to calculate dental?
 
Something like this.
=INDEX($B$14:$C$15,MATCH(INDEX($N$3:$N$5,MATCH(J15,$J$3:$J$5,0)),$A$14:$A$15,0),MATCH(INDEX($M$3:$M$5,MATCH(J15,$J$3:$J$5,0)),$B$13:$C$13,0))

But normally I'd just tuck it at end to table to simplify formula.
=INDEX($B$14:$C$15,MATCH(N3,$A$14:$A$15,0),MATCH(M3,$B$13:$C$13,0))

See attached.
 

Attachments

OK use Index MATCH as in the file

And I think Cell M4 should have PP not P ....

I'd agree with Chihiro, you can lay this data out in a simpler manner that will allow your formulas to be more concise - make the Data Table an Excel Table and you can use Structured Table References which will make your formulas very clear to follow ..
 

Attachments

YPJ,

Like @Chihiro, I prefer the index(match()) construction over the vlookup, but assuming that we keep the vlookup pattern from your original formula...

What about using this formula in K15:

=SUMPRODUCT((--($A$14:$A$15=VLOOKUP(J15,$J$3:$N$5,5,FALSE)))*(--($B$13:$C$13=VLOOKUP(J15,$J$3:$N$5,4,FALSE)))*$B$14:$C$15)

I've assumed @David Evans is right about the typo in M4; see attached.
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top