Need to run credit card checks versus database of invoices

hi all.

This been stumping me for a while. i have two reports that i can put on one sheet.
left report has a list of time, invoice number and amount for that invoice and on the right i have a list of data credit card entries
i can write a xlookup that takes data from right and check it on the left , but . if there is a gap between the invoices and day, i dont
know how to write the formula to skip the blank and to manually adjust the size of the query everytime for xlookup takes ages.

i will attach sample.

Running business 365 excel. so items like uniquee will work.

Thanks
chris.

Thanks. Seem to work.

Yeah. What i know is that the sequence of time say 17H00 for c/card 220 cant come on the xlookup query before 1700. lets say 3 transactions for the day was 220 . then condition of time in this case 1700 and condition of card number must be takein into account.
let me attach another sample to show you what i am talking about.

Originally Posted by p45cal

Are you sure?
Take cells I14 and I19, these are both for 220 and for Till 9700.
Looking for matches in the left table I find 3; cells C17, C21 & C35, all for 220 Till 9700.
Which to take?

I only came across this because I was experimenting with a Power Query solution and it threw up 3 possibilities for each of the 220 amounts.
So a little more direction perhaps?

i was thinking of adding conditionals as well but not sure how. they put the c/card numbers in sequential order. so lowest 5500 example will be
early morning and 5678 will be later in the day.

my aim is to eliminate card fraud. where they would put in a credit card, but have no corresponding invoice amount for the day.
Keep in mind the template we come up with has to be adapted to 3 different shops' data sets. Meaning hte card number in one shop is above 5500
and some shops in the low 2200 .

Both reports are run through power queries already on my side to eliminate the fluff of data-. Its two reports that have delivery charges on it as well. Ideally i would like to run a check on all tills 9700, all cards (with their numbers and values ) appear on the invoice data set, But doing it manually with just my normal looking i can find odds out a few months now. Eg the card of say 5500 that was supposed to be rung up after 5499 at 1756, doesnt exist. so from 1757 to closing that 5500 card at say value of 220 , has no invoice value.

hope that makes sense. Every time i explain it to my boss he goes glazed in the eyes. The process is time consuming and seeing as we are going to franchise the data
sets are just going to get bigger and more dense to deal with.
Hence the fraud to eliminate.

messing around with the formulas to see if xlookup can be run again if the time value is doubled

=IF(COUNTIF($Q$27:Q$35;Q27)>1;IFERROR(XLOOKUP(S27;$U$3:$U$145;$A$3:B$145);""))

Suffice it to say that is not the answer. The first countif check the original xlookup array returned from the card/invoice lookups in array and sees if there is double. then supposeldly has to run it again (eg ignore its first xlookup value and return the next similar value

But alas im way too new to nest if statements inside of xlookups atm.

fun fact. i have run countif on the range cards into the invoicing and it shows multiple entries - some even has 6 versions of the same card on the day.