Lookup value

[losamfr17]

Dear Wizards,

How would you solve the following challenge I'm facing using Excel formulas (as opposed to DAX)?
I need to know the rate associated with the payer given a HCPCS and a date range. Your suggestions are truly welcome. Thank you in advance!


[TABLE="width: 737"]
<tbody>[TR]
[TD]Payer[/TD]
[TD]HCPCs[/TD]
[TD]Start Date[/TD]
[TD] End Date[/TD]
[TD] Base Rate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0426[/TD]
[TD="align: right"]8/15/2016[/TD]
[TD="align: right"]1/1/2028[/TD]
[TD] $ 303.52[/TD]
[TD][/TD]
[TD]Payer[/TD]
[TD]HCPCs[/TD]
[TD]Service Date[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0427[/TD]
[TD="align: right"]8/15/2016[/TD]
[TD="align: right"]1/1/2028[/TD]
[TD] $ 480.57[/TD]
[TD][/TD]
[TD]Insurance A[/TD]
[TD]A0428[/TD]
[TD="align: right"]8/16/2016[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0428[/TD]
[TD="align: right"]8/15/2016[/TD]
[TD="align: right"]1/1/2028[/TD]
[TD] $ 252.93[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0429[/TD]
[TD="align: right"]8/15/2016[/TD]
[TD="align: right"]1/1/2028[/TD]
[TD] $ 404.69[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0433[/TD]
[TD="align: right"]8/15/2016[/TD]
[TD="align: right"]1/1/2028[/TD]
[TD] $ 695.56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0434[/TD]
[TD="align: right"]8/15/2016[/TD]
[TD="align: right"]1/1/2028[/TD]
[TD] $ 822.05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0426[/TD]
[TD="align: right"]8/15/2015[/TD]
[TD="align: right"]8/14/2016[/TD]
[TD] $ 294.22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0427[/TD]
[TD="align: right"]8/15/2015[/TD]
[TD="align: right"]8/14/2016[/TD]
[TD] $ 465.86[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0428[/TD]
[TD="align: right"]8/15/2015[/TD]
[TD="align: right"]8/14/2016[/TD]
[TD] $ 245.19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0429[/TD]
[TD="align: right"]8/15/2015[/TD]
[TD="align: right"]8/14/2016[/TD]
[TD] $ 392.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0433[/TD]
[TD="align: right"]8/15/2015[/TD]
[TD="align: right"]8/14/2016[/TD]
[TD] $ 674.27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance A[/TD]
[TD]A0434[/TD]
[TD="align: right"]8/15/2015[/TD]
[TD="align: right"]8/14/2016[/TD]
[TD] $ 796.87[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Re: LOOKUP VALUE CHALLENGE - Can't figure it out

ok, given that table begins in A1
Payer = Column A

=LOOKUP(2,1/((A2:A13=F3)*(B2:B13=G3)*(C2:C13<=H3)*(D2:D13>=H3)),E2:E13)

F3 = Insurance A
G3 = A0428
H3 = 8/16/2016

 

[losamfr17]

Re: LOOKUP VALUE CHALLENGE - Can't figure it out

ok, given that table begins in A1
Payer = Column A

=LOOKUP(2,1/((A2:A13=F3)*(B2:B13=G3)*(C2:C13<=H3)*(D2:D13>=H3)),E2:E13)

F3 = Insurance A
G3 = A0428
H3 = 8/16/2016

Holy smokes that's awesome!!
Can you please quickly explain to me what role the number 2 and the multiplication play in this formula?
Thank you so much. I feel like I'm gonna learn a lot from you on this one. I'm gonna take the time to learn how you did this.

 

[losamfr17]

Re: LOOKUP VALUE CHALLENGE - Can't figure it out

Holy smokes that's awesome!!
Can you please quickly explain to me what role the number 2 and the multiplication play in this formula?
Thank you so much. I feel like I'm gonna learn a lot from you on this one. I'm gonna take the time to learn how you did this.[/QUOTE]


Also, I tried the formula using a reference table, but it didn't work. Then I tried on a range and it worked perfectly.