Index and matching

rstlhiii

New Member
Joined
Apr 29, 2019
Messages
5
i'm trying to return a value that matches one criteria exactly, and the second by greater than or equal to. I created two tables below for reference, what I need to do is be able to match the ID # exactly, then find the Encounter date from the second table that is equal to or greater than the enrollment date in table 1, and return the value. Any help would be appreciated.


Table 1
ID NumberEnrollment Date
AR-AB20396*10/3/2018
AT-AB37900*2/22/2019
BC-AB1440710/5/2018
CC-AB14345*10/5/2018
CJ-AB14904*10/3/2018

<tbody>
</tbody>


Table 2
ID #ENCOUNTER DATEValue
AR-AB20396*9/12/20185.5
AT-AB37900*2/22/20195.3
BC-AB144075/4/20188.3
BC-AB144077/31/20187.7
BC-AB1440710/12/20187.7
BC-AB144073/4/20198.4
CC-AB14345*4/17/20187
CC-AB14345*7/30/20186.8
CC-AB14345*8/2/20186.8
CC-AB14345*12/4/20186.8
CJ-AB14904*1/17/20186.4
CJ-AB14904*5/13/20187
CS-AB354546/26/20186.9

<tbody>
</tbody>
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi & welcome to MrExcel.
How about


Excel 2013/2016
ABCJKLM
1ID NumberEnrollment DateID #ENCOUNTER DATEValue
2AR-AB20396*03/10/2018 AR-AB20396*12/09/20185.5
3AT-AB37900*22/02/20195.3AT-AB37900*22/02/20195.3
4BC-AB1440705/10/20187.7BC-AB1440704/05/20188.3
5CC-AB14345*05/10/20186.8BC-AB1440731/07/20187.7
6CJ-AB14904*03/10/2018BC-AB1440712/10/20187.7
7BC-AB1440704/03/20198.4
8CC-AB14345*17/04/20187
9CC-AB14345*30/07/20186.8
10CC-AB14345*02/08/20186.8
11CC-AB14345*04/12/20186.8
12CJ-AB14904*17/01/20186.4
13CJ-AB14904*13/05/20187
14CS-AB3545426/06/20186.9
List
Cell Formulas
RangeFormula
C2{=IFERROR(INDEX($M$2:$M$14,MATCH(1,($K$2:$K$14=A2)*($L$2:$L$14>=B2),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks for the quick response, your solution is very similar to one I had previous tried. Unfortunately I get the same result that it only returns a value for when the two dates are exact matches.
Here is my formula
=IFERROR(INDEX(Sheet2!$Q$3:$Q$10000,MATCH(1,(Sheet2!$A$3:$A$10000=A2)*(Sheet2!$D$3:$D$10000>=B2),0)),"")

Do you see where I went wrong, I am referencing two different sheets.
 
Upvote 0
That looks ok. Did you confirm it with CSE?
Also are you sure that your dates are real dates, rather than text?
 
Upvote 0
It looks like I got something to work, My original table didn't always have a number placed in the value column. Looks like that might have been throwing things off. Even though i don't understand why.
Thanks for your help.
 
Upvote 0
Glad you got it sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top