Lookup from multiple worksheets and return result

shili12 · Nov 22, 2024

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 · Nov 22, 2024

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 · Nov 22, 2024

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 · Nov 22, 2024

I have standardised your tab names as mmmyyyy.

This in F3:

Code:

=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 · Nov 22, 2024

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 · Nov 22, 2024

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 · Nov 22, 2024

This uses column C instead:

Code:

=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 · Nov 23, 2024

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 · Nov 23, 2024

I think this works.

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

Code:

=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 · Nov 23, 2024

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

Code:

=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 · Nov 23, 2024

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:

Code:

=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 · Nov 23, 2024

shili12 said:
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:

AliGW · Nov 24, 2024

I got a like - does that mean that post #11 worked for you? If so, then an acknowledgement here in the thread would have been nice.

shili12 · Nov 24, 2024

AliGW said:
I got a like - does that mean that post #11 worked for you? If so, then an acknowledgement here in the thread would have been nice.

I haven't reported to office today since it's a weekend, until tomorrow. All the data is in office PC. I will review it tomorrow and provide a feedback. Two of the juniors were stuck with a process to view 2000 records from 100000 rows per month from may 2023 till Nov 2024. Meanwhile I noted start end worksheets too.

AliGW · Nov 24, 2024

the juniors were stuck with a process to view 2000 records from 100000 rows per month from may 2023 till Nov 2024

Why do you keep on repeating this? We understand what you are trying to avoid, and my solution should (and hopefully will) make this unnecessary.

shili12 · Nov 25, 2024

AliGW said:
Why do you keep on repeating this? We understand what you are trying to avoid, and my solution should (and hopefully will) make this unnecessary.

after modifying your formula

AliGW · Nov 25, 2024

You need to take more care. You have messed up the end of the formula. Look:

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

WORKBOOK ATTACHED

shili12 · Nov 25, 2024

regretful it does not return the desired result, see screenshot.

on a brighter note, it returned results of 15 regno with your lambda

AliGW · Nov 25, 2024

I can't troubleshoot a workbook I have no access to. I can't troubleshoot from screenshots. Where it returns "Not Found", there is no match. Provide a workbook with that error and I'll see if I can tell you why, but if there is no exact match, then it's your data that isn't consistent.

As I showed, it works in the sample workbook you provided.

shili12 · Nov 25, 2024

am only using your workbook attachment given by you,no other,placing real data for the company, using Ctrl F to see in reality if the data exist against the results you give (it does but your result says not found), , then instead of 2000 lines, i decided to try with 30 lines,
modified your code due to 100000 rows,
column K vehicle no, column m=end date(prefer date of issuance), column U=value of vehicle, i believe all should have 100000 rows instead of what you wrote 100 in your formula. also in name manager the rows go upto A2:X 54050, it would be great if you modified your worksheet accordingly.
how can i send you my the real workbookin a public forum ?

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

AliGW · Nov 25, 2024

You need to create a sample workbook using some of the data from the real workbook, but desensitised. Remove data from columns that are not needed in the formula. The sample workbook needs to show the problem.

AliGW · Nov 25, 2024

OK - I am looking at your workbook. What is the likelihood of ANY of the month sheets having one million rows of data?

shili12 · Nov 25, 2024

not possible !!
just amend A2:X54050 , then i'll try it again, 100 rows are too little.

AliGW · Nov 25, 2024

Done that - it's slow, but it works. What is the problem?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2

[/td][td]REGNO[/td][td]MONTH INSPECTED[/td][td]Data is in this month[/td][td]sum insured[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3

[/td][td=bgcolor:#E2EFDA]KCP146M[/td][td=bgcolor:#E2EFDA]

Aug-24

[/td][td]Aug2024[/td][td]

0.00