VLOOKUP FUNCTION?

[jpalleyne]

I need a formula to return the ref # from table #2 so I have the ref # in table #1. I'd need the formula to be based on date and and amount as those would be the only unique identifiers given that the numbers have the potential to repeat themselves, that's where the date criteria would come in but there's still a chance that the same number will be present on that particular date. I was trying VLOOKUP/MATCH with no luck.
Any help would be appreciated.

​

Table #1

Date​	Details​	Amount​	Ref #​
01/03/2023​	Digital- SOUTH COAST DENTmed i​	2,368.26​	?​
​	​	​	​

​

Table #2

Date​	Details​	Amount​	Ref #​
01/03/2023​	Wire Transfer	2,368.26​	12614​
​	​	​	​

 

[shift-del]

Hello

=XLOOKUP([@Date]&[@Amount],table2[Date]&table2[Amount],table2[Ref '#])'

If Ref # is always a number you can use SUMFIFS().

=SUMIFS(table2[Ref '#];table2[Date];[@Date];table2[Amount];[@Amount])'

 

[Peter_SSs]

but there's still a chance that the same number will be present on that particular date.

Not quite sure what that means so if the previous post does not fully answer your question, please post some larger sample data (say 15-20 rows) and the expected results with XL2BB and explain again in relation to that larger sample data

 

[jpalleyne]

Thanks - I should have put some more data. Here's how the info looks:

Table #1

Date	Details	Amount	Ref #
01/03/2023	Digital- SOUTH COAST DENTmed i	2,368.26	?
01/03/2023	Insurance	300.00	?
01/03/2023	Insurance	300.00	?
01/04/2023	Insurance	6,989.30	?
01/04/2023	Insurance	200.00	?
01/04/2023	Insurance	200.00	?

Table #2

Date	Details	Amount	Ref #
01/03/2023	Wire Transfer	2,368.26	12614
01/03/2023	Wire Transfer	300.00	12614
01/03/2023	Wire Transfer	300.00	12614
01/04/2023	Wire Transfer	6,989.30	12615
01/04/2023	Wire Transfer	200.00	12615
01/04/2023	Wire Transfer	200.00	12615

So the ref # repeats in table #2 and the date will be the same for a couple lines and the numbers will be the same or different.

The SUMIFS is adding ref #s together for the amounts that repeat as is expected. I hope this helps!

 

[Peter_SSs]

The SUMIFS is adding ref #s together for the amounts that repeat as is expected. I hope this helps!

.. but does the XLOOOKUP formula do what you want?

 

[jpalleyne]

XLOOKUP is returning an error. Can you send what the formula for XLOOKUP should look like with the new data set showing the multiple lines, I may be doing it wrong.

 

[jpalleyne]

Not quite sure what that means so if the previous post does not fully answer your question, please post some larger sample data (say 15-20 rows) and the expected results with XL2BB and explain again in relation to that larger sample data

I posted some more data - please take a look.

 

[dermie_72]

is there an opportunity to have different ref# on the same date?

 

[jpalleyne]

is there an opportunity to have different ref# on the same date?

No it would be one ref# per date in this case.

 

[Peter_SSs]

XLOOKUP is returning an error.

I had missed that the XLOOKUP formula in post 2 contains a stray single quote mark (') at the end.

Can you send what the formula for XLOOKUP should look like with the new data set showing the multiple lines,

The formula structure proposed (even with the extra ' removed) is somewhat problematic to me as it could lead to false results as shown in the first mini sheet below.

NB: When posting sample data, please consider XL2BB rather than how you have done it in posts 1 & 4 with all those extra borders and no column/row labels etc.

jpalleyne.xlsm
	A	B	C	D
2
3	Date	Details	Amount	Ref #
4	1/03/2023	Digital- SOUTH COAST DENTmed i	2,368.26	12614
5	1/03/2023	Insurance	300	12614
6	1/03/2023	Insurance	300	12614
7	1/04/2023	Insurance	6,989.30	12615
8	1/04/2023	Insurance	200	12615
9	1/04/2023	Insurance	200	12615
10	24/04/1912	xx	11	44
11
12
13	Date	Details	Amount	Ref #
14	1/03/2023	Wire Transfer	2,368.26	12614
15	1/03/2023	Wire Transfer	300	12614
16	1/03/2023	Wire Transfer	300	12614
17	1/04/2023	Wire Transfer	6,989.30	12615
18	1/04/2023	Wire Transfer	200	12615
19	1/04/2023	Wire Transfer	200	12615
20	24/02/2023	xx	1	44
21
Sheet1

Cell Formulas
Range	Formula
D4:D10	D4	=XLOOKUP([@Date]&[@Amount],Table2[Date]&Table2[Amount],Table2[Ref '#])

To overcome the possible incorrect results like above, I would modify the formula like this

jpalleyne.xlsm
	A	B	C	D
2
3	Date	Details	Amount	Ref #
4	1/03/2023	Digital- SOUTH COAST DENTmed i	2,368.26	12614
5	1/03/2023	Insurance	300	12614
6	1/03/2023	Insurance	300	12614
7	1/04/2023	Insurance	6,989.30	12615
8	1/04/2023	Insurance	200	12615
9	1/04/2023	Insurance	200	12615
10	24/04/1912	xx	11	#N/A
11
12
13	Date	Details	Amount	Ref #
14	1/03/2023	Wire Transfer	2,368.26	12614
15	1/03/2023	Wire Transfer	300	12614
16	1/03/2023	Wire Transfer	300	12614
17	1/04/2023	Wire Transfer	6,989.30	12615
18	1/04/2023	Wire Transfer	200	12615
19	1/04/2023	Wire Transfer	200	12615
20	24/02/2023	xx	1	44
21
Sheet1

Cell Formulas
Range	Formula
D4:D10	D4	=XLOOKUP([@Date]&"|"&[@Amount],Table2[Date]&"|"&Table2[Amount],Table2[Ref '#])