Reverse lookup with multiple criteria

[Showl17]

Hello,
How do you create a formula where it has multiple search criteria? We are a facility that charges a one time admin fee per person that is valid for a year. How do you create a formula that will do a reverse look-up for the name (column A) and date (column B) and return a value ($50) in column C only if it hasn't been previously billed within the last 365 days? Our current spreadsheet is thousands of rows and new data is added weekly. Attached is a simplified example. To make it more complicated, Column C already has an existing formula that would only populate if specific cells had data: =IF(AND(ISBLANK(D2:H2)),"",IF(SUM(D2:H2)=0,"",50)).

Any help would be appreciated. Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Is this what you mean? I have assumed that entries for a particular name are chronological down the sheet. That is, when looking at any particular row, the most recent previous entry for that person will be the first entry that you come to for that person working up the sheet from the row you are looking at. If that is not the case we can do it a different way.

21 05 22.xlsm
	A	B	C	D	E	F	G	H
1	Name	Date	Admin Fee	T1	T2	T3	T4	T5
2	N1	02-Mar-20	50	20		35
3	N2	02-May-20	50		15	35	10
4	N3	15-Jun-20	50	20	15			50
5	N4	14-Jun-20	50		15		10
6	N5	31-Jul-20	50		15		10
7	N1	31-Dec-20						50
8	N4	02-Feb-21		20		35
9	N5	20-Aug-21	50			35
Admin Fee

Cell Formulas
Range	Formula
C2:C9	C2	=IF(OR(SUM(D2:H2)=0,XLOOKUP(A2,A$1:A1,B$1:B1,0,,-1)>EDATE(B2,-12)),"",50)

 

[Rahul1987]

@Peter

can we do this if we do not have 365 version? pls suggest whether I am correct?

as

Hello,
How do you create a formula where it has multiple search criteria? We are a facility that charges a one time admin fee per person that is valid for a year. How do you create a formula that will do a reverse look-up for the name (column A) and date (column B) and return a value ($50) in column C only if it hasn't been previously billed within the last 365 days? Our current spreadsheet is thousands of rows and new data is added weekly. Attached is a simplified example. To make it more complicated, Column C already has an existing formula that would only populate if specific cells had data: =IF(AND(ISBLANK(D2:H2)),"",IF(SUM(D2:H2)=0,"",50)).

Any help would be appreciated. Thanks!


View attachment 39217

as per your dates none of them have completed 365 days yet then why you have put 50 against them? did not understand your question

Book1
	A	B	C
1	Name	Date	Days
2	N1	2-Mar-20	320.00
3	N2	2-May-20	275.00
4	N3	15-Jun-20	245.00
5	N4	14-Jun-20	245.00
6	N5	31-Jul-20	211.00
7	N1	31-Dec-20	102.00
8	N4	2-Feb-21	79.00
9	N5	20-Aug-21	-65.00
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=NETWORKDAYS(B2,TODAY())

 

[Peter_SSs]

can we do this if we do not have 365 version? pls suggest whether I am correct?

Rather than hijack this thread, I suggest that you start your own,