LinkBack URL
About LinkBacks
Hi,
How can I extract the data of Y column,Z column and AC column data by matching F column of of L3 sheet from P3 Sheet by matching B column and pulling BH,BI and BJ.
Hi,
How can I extract the data of Y column,Z column and AC column data by matching F column of of L3 sheet from P3 Sheet by matching B column and pulling BH,BI and BJ.
Last edited by paradise2sr; 02-01-2024 at 09:14 PM.
Clean all expected results.
L3!Y10
=DROP(REDUCE(0,FILTER(F10:F100,F10:F100<>""),LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), HSTACK(CHOOSECOLS(a,2,3),"","",INDEX(a,1)))))),1)
I want Y ,Z column simultaneously and AC column separately formula but here it has spilled across in all columns towards right.As there are some data in AA and AB column but in above I have mentioned blank.So I don't want to spill at once.
Y10
Z10=DROP(REDUCE(0,FILTER(F10:F100,F10:F100<>""),LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), INDEX(a,2))))),1)
Ac10=DROP(REDUCE(0,FILTER(F10:F100,F10:F100<>""),LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), INDEX(a,3))))),1)
=DROP(REDUCE(0,FILTER(F10:F100,F10:F100<>""),LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), INDEX(a,1))))),1)
In F column, if I insert a row suppose at F11 or F12 or anywhere then it throws a wrong result.So spilling formula below would seems incorrect.This issue is there.There will be some data including blanks where it doesn't match with P3. Sheet also.
I have intentionally shown F column data i.e matching criteria without blank in between and non match data but in actual it does exist.
I hope u understand what I mean to say.So in this type case it will not work.I hope u can revise accordingly.
Last edited by paradise2sr; 01-20-2024 at 11:56 AM.
How about these?
Y10
AC10=DROP(REDUCE(0,F10:F101,LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), IF(y="",""=DROP(REDUCE(0,F10:F101,LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), IF(y="","",INDEX(a,3)))))),1) ,INDEX(a,2)))))),1)
Z10=DROP(REDUCE(0,F10:F101,LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), IF(y="","",INDEX(a,1)))))),1)
Pls see the file it does not seems to works.I have now added a blank and non match data as well.It shows CalC error.
Try this in Y10. Z10 and AC10 are similar formulas.
=DROP(REDUCE(0,F10:F100,LAMBDA(x,y,VSTACK(x,LET( a,TOROW(IF(ISNUMBER(SEARCH('P3'!B7:B15,y)),'P3'!BH7:BJ15,NA()),3), IFERROR(IF(y="","",INDEX(a,2)),""))))),1)
Hi,
Thanx for the response.
I have seen and altered the ranges to suits my file but found that it doesn't pull the data.
Plz refer the attachment.I have altered the ranges and found that it threw zero and thus fails to work.
Index and match works perfectly in case of S-1 and the like but in case such as B-1/2/3 or Lon-2/5 it fails to work might be due to find,combine and match which seems to be complicated.I hope u understand.
A versality in formula would be highly appreciated.
Last edited by paradise2sr; 02-01-2024 at 09:12 PM.
I think you don't need to spill formula.
Y10
Z10=IF($F10="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH('P3'!$B$7:$B$16,$F10)),'P3'!$BH$7:$BJ$16,NA()),3),2),""))
AC10=IF($F10="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH('P3'!$B$7:$B$16,$F10)),'P3'!$BH$7:$BJ$16,NA()),3),3),""))
All copied down.=IF($F10="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH('P3'!$B$7:$B$16,$F10)),'P3'!$BH$7:$BJ$16,NA()),3),1),""))
No spill formula is good but on altering the range it gives me zero result.This is why I avoid.
Can you explain why on Y3 if I put below function
It gives zero (0).I have simply extended the range.IF($F10="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH('P3'!$B$5:$B$2100,$F10)),'P3'!$BH$5:$BJ$2100,NA()),3),2),""))
B-1/2/3 how does this matching works from Sheet P3.
However One of the formula as mentioned below works for summing but matching it does not apply.
I am little bit confused as I am unable to implement it.However the structure is same simply all over I have other data which I haven't disclosed in demo file.LET(c,SUBSTITUTE(F40,"/"," "&LEFT(F40,FIND("-",F40))),SUM(SUMIFS('P3'!CC:CC,'P3'!B:B,INDEX(TRIM(MID(SUBSTITUTE(c," ",REPT(" ",99)),((SEQUENCE(LEN(c)-LEN(SUBSTITUTE(c," ",""))+1,,1)-1)*99)+1,99)),))))
Try this in Y10, if you want add range.
Z10=LET( a,CHOOSECOLS(FILTER('P3'!$B$7:$BJ$2100,'P3'!$B$7:$B$2100<>""),1,59,60,61), IF($F10="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1),$F10)),a,NA()),3),3),"")) )
AC10=LET( a,CHOOSECOLS(FILTER('P3'!$B$7:$BJ$2100,'P3'!$B$7:$B$2100<>""),1,59,60,61), IF($F10="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1),$F10)),a,NA()),3),4),"")) )
All copied down.=LET( a,CHOOSECOLS(FILTER('P3'!$B$7:$BJ$2100,'P3'!$B$7:$B$2100<>""),1,59,60,61), IF($F10="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1),$F10)),a,NA()),3),2),"")) )
Thanx Windknife
This #Post 12 works but why your all above spill formula does not worked.
Can you make above formula #Post 12 as 2 spill formula for below :
1. Y10:Z10 & below > 1 spill formula &
2.AC10 & below another 2nd spill formula
Since # Post 12 has worked perfectly in my real file.
Last edited by paradise2sr; 01-21-2024 at 05:07 AM.
Clean all expected results.
Y10
AC10=LET( a,CHOOSECOLS(FILTER('P3'!$B$7:$BJ$2100,'P3'!$B$7:$B$2100<>""),1,59,60,61), y,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1),x)),a,NA()),3),3),"")))), z,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1),x)),a,NA()),3),4),"")))), HSTACK(y,z) )
=LET( a,CHOOSECOLS(FILTER('P3'!$B$7:$BJ$2100,'P3'!$B$7:$B$2100<>""),1,59,60,61), MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1),x)),a,NA()),3),2),"")))) )
This seems perfect.Thanx
You are welcome.
Hi,
Is there alternative formula to Post 14 without use of LAMBDA and MAP function.
What's the problem with LAMBDA and MAP?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Seems sorts of displaying the result only in first row only.Remaining rows does not display the result .I am trying to figure out.
They work fine in the workbook attached to post #14, spilling correctly. If you are having problems, attach a workbook that shows them.
Yes as per post it's fine.
Kindly assume that I am working in Office 2021.How the same result would be received ?
Ok .Here I have attached file with two more worksheets namely L1 and P1 besides L3 and P3.In L1 same functions is copied from L3 sheet but referencing worksheet from P3 is altered to P1 but results are not properly extracted.So,kindly see why there is zero even if there is data in P1 worksheet.Red highlighted tab L1 and P1 should be looked upon whereas L3 and P3 are fine.
I am very much surprised why it is not working when adding the worksheets and changing the worksheet references.Plz see what is the cause of issue.I have many other sheets to add.
Last edited by paradise2sr; 02-01-2024 at 09:10 PM.
Your forum profile should show the OLDEST version of Excel that any solution should work with - please update your forum profile to Excel 2021. Thanks.Kindly assume that I am working in Office 2021.
Of course these 365 formulae won't work in 2021, but they do work in 365, which is the version they were written for.
AliGW on MS365 Beta Channel (Windows 11) 64 bit
<Not exteacting by Formula> <Not exteacting by Formula> <<< Extract >> <<< Extract >> <<< Extract >> MIK,KALE Under JS DOB,PATA Under JS CSR,KALE Under JS DOB,PATA Under JS CSR,KALE Under JS CSR,KALE Under JS CSR,KALE Under JS
Sheet: L1
Last edited by AliGW; 02-02-2024 at 03:04 AM.
Amended formulas.
Y10
AC10=LET( a,CHOOSECOLS(FILTER('P1'!$B$5:$BJ$2100,'P1'!$B$5:$B$2100<>""),1,59,60,61), y,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE(x,"/"," ")&" ")),a,NA()),3),3),"")))), z,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE(x,"/"," ")&" ")),a,NA()),3),4),"")))), r,HSTACK(y,z), IF(r=0,"",r) )
=LET( a,CHOOSECOLS(FILTER('P1'!$B$7:$BJ$2100,'P1'!$B$7:$B$2100<>""),1,59,60,61), b,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE(x,"/"," ")&" ")),a,NA()),3),2),"")))), IF(b=0,"",b) )
Hi,windknife,Originally Posted by windknife
Amended formulas.
Y10
AC10=LET( a,CHOOSECOLS(FILTER('P1'!$B$5:$BJ$2100,'P1'!$B$5:$B$2100<>""),1,59,60,61), y,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE(x,"/"," ")&" ")),a,NA()),3),3),"")))), z,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE(x,"/"," ")&" ")),a,NA()),3),4),"")))), r,HSTACK(y,z), IF(r=0,"",r) )
=LET( a,CHOOSECOLS(FILTER('P1'!$B$7:$BJ$2100,'P1'!$B$7:$B$2100<>""),1,59,60,61), b,MAP(F10:F100,LAMBDA(x,IF(x="","",IFERROR(INDEX(TOROW(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE(x,"/"," ")&" ")),a,NA()),3),2),"")))), IF(b=0,"",b) )
I think you have done it finally.Lateron on night I will check the accuracy of data but seems to have been done.
Thanx again for your kind effort.
Last edited by AliGW; 02-02-2024 at 04:54 AM. Reason: Profile has been updated - post restored.
CHOOSECOLS and MAP are not available in Excel 2021. Neither is HSTACK.
Yes.Its not available.
For 2021 version.
Y10
AC10=LET( a,'P1'!$B$5:$BJ$2100, b,MIN(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE($F10,"/"," ")&" ")),ROW($B$5:$BJ$2100),"")), c,IF($F10="",{"",""},INDEX(a,b-4,{60,61})), IF(c=0,"",c) )
Both copied down.=LET( a,'P1'!$B$5:$BJ$2100, b,MIN(IF(ISNUMBER(SEARCH(INDEX(a,,1)&" ",SUBSTITUTE($F10,"/"," ")&" ")),ROW($B$5:$BJ$2100),"")), c,IF($F10="","",INDEX(a,b-4,59)), IF(c=0,"",c) )
You still haven't updated your forum profile - do this NOW.
I have both version.
That's not the point: you want solutions to work with Office 2021, but you have 365 in your profile. This means that members are wasting time on 365 solutions that have now been rejected.
NO FURTHER ASSISTANCE to be offered until the OP's profile has been changed to Excel 2021.
Thanks.
Ok I have added both version.However,windknife has already seems to have solved the issue.
If any issues related with this if appears will post accordingly.Meanwhile I am marking this as solved
It can't have as it won't work in Office 2021. But if you're happy ...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks