• 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

YPJunk

YPJunk

Member
Hello!

Hope all is well. I cannot figure out the appropriate formula to use to return a value based on two criteria.
 

Attachments

  • Excel Question.xlsx
    13.4 KB · Views: 6
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

  • Excel Question - DME.xlsx
    13.5 KB · Views: 3
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

  • Excel Question.xlsx
    13.8 KB · Views: 10
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

  • Excel Question - DME.xlsx
    13.5 KB · Views: 5
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

  • ypj1.xlsx
    13.5 KB · Views: 5
Last edited:
Thank you guys so much! I agree a simpler table is best. I really appreciate it!

Sincerely,

Yvette
 
You must log in or register to reply here.
Back
Top