Return Item with Multiple Search Criteria

[cmenconi]

Hello-

I have an excel workbook with two tabs. One data set including transactions with relevant data columns: Account Number & Amount. The second tab has a comprehensive export of data that I am trying to match with the first tab of data. The relevant data columns are included as well as a date in which the transaction was processed. I am trying to use a formula to return the transaction date on tab 1 if the formula find a match with the account number and the amount. The account number will have multiple matches since many items are posted to each account, however with the additional parameter of dollar amount it should narrow it down to one result. From that one result I would like the date from tab 2 to be returned on tab 1, this will be a data validation point for me to verify if the transaction was processed. One the data source tab I have named the data area for the various fileds: the entire table of data is "transaction_data", the amount is named "data_amount" which should match with tab one amount, account number is named "data_account" which will match with the account number on the given row but will have multiple matches in the source data, transaction date is named "data_date" which is the specific date I am trying to return in my data set in a new column that I created. Thanks!

 

[etaf]

you could use

index( column to return date , match ( account cell & amount cell, rangeAccount&rangeAmount,0))

a simple example

Note if there are 2 values - it will only return the 1st found

But with excel version 365 - they maybe a way to do it with SORT/FILTER

Book1
	A	B	C	D	E	F	G
1	account	Amount	lookpdate		Date	Account	Amount
2	1	10.78	1/6/22		1/1/22	1	10.13
3	3	10.52	1/4/22		1/2/22	1	10.26
4	4	11.29	#N/A		1/3/22	2	10.52
5	2	10.52	1/3/22		1/4/22	3	10.52
6			#N/A		1/5/22	1	10.65
7					1/6/22	1	10.78
8					1/7/22	2	10.91
9					1/8/22	1	11.04
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX($E$2:$E$9,MATCH(A2&B2,$F$2:$F$9&$G$2:$G$9,0))