• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup from multiple worksheets and return result

shili12

Member
Raw dataRaw dataResult required from worksheetsResult required from worksheets
REGNOMONTH INSPECTED
Data is in this month
sum insured
KDQ030FSep-24
Sep2024​
800,000.00
KDJ823POct-24
Sep2024​
600,000.00
KDH837QSep-24
Sep2024​
800,000.00
KDM908ESep-24
Sep2024​
450,000.00
KCQ332ZSep-24
Sep2024​
400,000.00
KDP632LJun-24
Jun2024​
850,000.00
1NZ8494932Jun-24
Jun2024​
1,000,000.00
KDE891PJun-24
Jun2024​
600,000.00
KDD982GJun-24
Jun2024​
600,000.00

just a request to lookup from monthly worksheet and return a result.
thanks in advance.
 
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.
 
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.
 

Attachments

  • askquestion.xlsb
    74.7 KB · Views: 5
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.
 

Attachments

  • shili12 askquestion(1) SPILL 365 AliGW.xlsb
    75.6 KB · Views: 4
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.
1732281837623.png
also i need a similar formula, for this column as well. thanks for the effort.
1732281743142.png
 

Attachments

  • solutionAliGW.xlsb
    82.6 KB · Views: 2
1732288326755.png

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?
 

Attachments

  • Chandoo58004.xlsx
    21.4 KB · Views: 3
Last edited:
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.
 
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.
 
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.
 

Attachments

  • shili12 askquestion(1) SPILL 365 AliGW.xlsb
    75.7 KB · Views: 2
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))
 

Attachments

  • Chandoo58004 (1).xlsx
    21.4 KB · Views: 4
Last edited:
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)))))
 
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:

1732370525075.png
 
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 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.
 
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.
 
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
 

Attachments

  • shili12 askquestion(1) SPILL 365 AliGW.xlsb
    75.7 KB · Views: 2
regretful it does not return the desired result, see screenshot.
Screenshot 2024-11-25 112010.png

on a brighter note, it returned results of 15 regno with your lambda
Screenshot 2024-11-25 112603.png
 

Attachments

  • Screenshot 2024-11-25 112010.png
    Screenshot 2024-11-25 112010.png
    74.6 KB · Views: 0
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.
 
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))
 

Attachments

  • shili12 askquestion(1) SPILL 365 AliGW (2).xlsb
    79.8 KB · Views: 1
Last edited:
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.
 
OK - I am looking at your workbook. What is the likelihood of ANY of the month sheets having one million rows of data?
 
Done that - it's slow, but it works. What is the problem?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

A
B
C
D
2
REGNOMONTH INSPECTEDData is in this monthsum insured
3
KCP146M
Aug-24​
Aug2024
0.00​
4
KDM718D
Sep-24​
Aug2024
850,000.00​
5
KDL922B
Aug-24​
Aug2024
0.00​
6
KDK276T
Aug-24​
Aug2024
0.00​
7
KCP995G
Aug-24​
Aug2024
0.00​
8
KDB988G
Sep-24​
Aug2024
0.00​
9
KCW750W
Aug-24​
Aug2024
400,000.00​
10
KDL755P
Aug-24​
Aug2024
1,000,000.00​
11
KDC475A
Aug-24​
Aug2024
0.00​
12
KDE179Q
Aug-24​
Aug2024
500,000.00​
Sheet: query
 
Back
Top