Lookup from multiple worksheets and return result

[shili12]

Raw data	Raw data	Result required from worksheets	Result required from worksheets
REGNO	MONTH INSPECTED	Data is in this month​	sum insured
KDQ030F	Sep-24	Sep2024​	800,000.00
KDJ823P	Oct-24	Sep2024​	600,000.00
KDH837Q	Sep-24	Sep2024​	800,000.00
KDM908E	Sep-24	Sep2024​	450,000.00
KCQ332Z	Sep-24	Sep2024​	400,000.00
KDP632L	Jun-24	Jun2024​	850,000.00
1NZ8494932	Jun-24	Jun2024​	1,000,000.00
KDE891P	Jun-24	Jun2024​	600,000.00
KDD982G	Jun-24	Jun2024​	600,000.00

just a request to lookup from monthly worksheet and return a result.
thanks in advance.

 

[AliGW]

Which version of Excel?

Your workbook full of real E-mail addresses and other personal details. This may be in breach of data protection laws in your country. This is a public forum.

 

[shili12]

office 365 , as always VBA and power query solutions are welcome too.
have deleted rest of data but much of it is conconted and not sincere.

 

[AliGW]

I have standardised your tab names as mmmyyyy.

This in F3:

=MAP(A3:A17,B3:B17,
LAMBDA(x,y,
LET(s,SUBSTITUTE(LOWER(y),"-","20"),
k,INDIRECT("'"&s&"'!K:K"),
u,INDIRECT("'"&s&"'!U:U"),
XLOOKUP(x,k,u,""))))

You have a couple of rows that don't match - check for leading and trailing spaces there.

 

[shili12]

You have a couple of rows that don't match - check for leading and trailing spaces there.

actually all are to match, you need to redefine the column, ideally it should match as i got data directly from monthly worksheets. the raw data is from third party, we want to validate from our side.

also i need a similar formula, for this column as well. thanks for the effort.

 

[p45cal]

With Power Query, it's better if you interrogate your askquestion workbook from a separate workbook because it's more difficult to interrogate the workbook that the query is in.
A few points about that file:

• I've reinstated the header Email on all sheets (I think you deleted that column and its header).
• Preferably, remove the query sheet (I've filtered it out anyway).
• Save it as a .xlsm file (or.xlsx); there's more info available to PQ than a .xlsb file.

In the attached is a workbook with your table (as in your query sheet) and next to it the result table from PQ.
Note:

• that it only uses the regno to do the matching, so if there are multiple similar regnos present on any sheet it will show them all.
• there are 2 instances of regno KDQ660C on your query table AND there are 2 instances of the same regno on the jun2024 sheet of the askquestion workbook (one Cancelled, the other Active), hence there are 4 rows of results for that no. in the results table. edit: see also 1NZ8494932.
• I've highlighted the one instance where the regno (KDJ823P) was found on a different sheet from expected. edit: there's more than one instance, there are 3!

Is this what you're after?

 

[AliGW]

This uses column C instead:

=MAP(A3:A17,B3:B17,
LAMBDA(x,y,
LET(s,LOWER(C3),
k,INDIRECT("'"&s&"'!K:K"),
u,INDIRECT("'"&s&"'!U:U"),
XLOOKUP(x,k,u,""))))

It returns no matches in June.

It's not my job to work out why matches aren't happening - that's up to you.

 

[shili12]

Umm.. column A would be best ? Or I attempt to modify from my side, rather. Let me see how your solution works out, in addition to pascal.
The raw data is list sent by assessor, my job is to check regno of vehicle whether it appears in monthly record worksheet with closest date. The assessor can value the vehicle on Oct 1st but on monthly worksheet a certificate license can be issued on 30th sept.Imagine I was doing it one by one 2000 lines. The monthly worksheet in reality are over 100000 rows and not 30 in example.

 

[AliGW]

I think this works.

Remove ALL expected results from C3 onwards, then in C3 followed by ENTER:

=LET(reg,VSTACK(start:finish!K2:K100),
date,VSTACK(start:finish!M2:M100),
sum_insured,VSTACK(start:finish!U2:U100),
DROP(REDUCE("",TOCOL(A3:A100,1),LAMBDA(x,y,
VSTACK(x,TAKE(FILTER(HSTACK(TEXT(date,"Mmmyyyy"),sum_insured),reg=y,"Not Found"),1)))),1))

I have added two blank sheets - start and finish. These must remain in situ and be around the lookup sheets, so if you add more months, add them in chronological order before finish (and after start). Edit the ranges for a bit more than the maximum you'll need. Do NOT try to use full column references, as it won't work.

 

[shili12]

thank you , it looks like a viable solution, let me try it, imagine, going thru 2000 lines , one by one ! looking at 100000 rows in one month.
meanwhile @p45cal , where are the months in the workbook ?
i tried it for 100000 rows but it does not give desired result, i changed this part

=LET(reg,VSTACK(start:finish!K2:K100),
date,VSTACK(start:finish!M2:M100),
sum_insured,VSTACK(start:finish!U2:U100),
DROP(REDUCE("",TOCOL(A3:A100000,1),LAMBDA(x,y,
VSTACK(x,TAKE(FILTER(HSTACK(TEXT(date,"Mmmyyyy"),sum_insured),reg=y,"Not Found"),1)))),1))

 

[AliGW]

The attachment does not contain my formula, so I have no way of checking it for you. In what way is it NOT doing what you want?

One guess:

=LET(reg,VSTACK(start:finish!K2:K100),
date,VSTACK(start:finish!M2:M100),
sum_insured,VSTACK(start:finish!U2:U100),
DROP(REDUCE("",TOCOL(A3:A100000,1),LAMBDA(x,y,
VSTACK(x,FILTER(HSTACK(TEXT(date,"Mmmyyyy"),sum_insured),reg=y,"Not Found"),1)))))

 

[p45cal]

meanwhile @p45cal , where are the months in the workbook ?

If by the months you mean the sheets, they're on your system in the workbook askquestion.
I recommend saving your the askquestion workbook as an xlsx or xlsm workbook (don't just change the extension, you have to do a save as) after removing the query worksheet from it. Then you need to alter the first step of the askquestionxlsb query to point to wherever you've saved it on your system: