•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Is it possible to rewrite this formula to not have to retype every xlookup?
Our take
Are you looking to simplify your Excel formulas when using XLOOKUP? If you need to retrieve corresponding percentages for multiple columns—up to 20, in some cases—it's essential to streamline your approach. In this scenario, you're encountering a CALC error with your current use of the BYCOL and LAMBDA functions. While VBA isn't an option, there are alternative strategies to help you efficiently aggregate your data across years. Let's explore possible solutions that can enhance your formula and simplify your workflow without unnecessary complexity.
Example data and intended result
For the data in C2:G12 I need to find the corresponding percentages of X,Y,Z for each of A:D and then add them all together for each year. Ideally I'd like to simplify this formula since in some cases I have up to 20 columns to retrieve the components for.
I've tried =BYCOL(D2:G2,LAMBDA(x, XLOOKUP(x,$I$2:$I$5,$J$2:$L$5,,0))) but I receive a CALC error and I'm really not experienced with using LAMBDA in excel in this way. Unfortunately I cannot use VBA for this particular problem.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Auto-Updating Formula to reflect YTD percentageHey Gang of Excel Gurus. Could not find the answer to this in search, probably because I don't know how to exactly describe the function I am looking for... So I have a sales spreadsheet similar to the sample one attached here. The question is how to create a formula to reflect a comparison sum that is a "rolling" total in column B (2025) so that I can compare as a Year to Date with Column C (2026). In my example, I have sales numbers for this year, thus far. i want to know the percentage of what it was for the same period last year. I want the percentage to update so that when I eventually enter a value in for April, the sum for Jan-Apr in 2025 will update and thereby reflect the updated percentage in a different cell. Therefore: As of today the YTD percentage is.... Is there a way to do this? To have a formula update somehow when new data is entered in a different cell, or set of cells? submitted by /u/loozrrtuba [link] [comments]
- Am I not using XLOOKUP correctly? And absolutes $shttps://imgur.com/a/o0alvvv I am basically trying to get column "E" (Billing End) on the "Upload sheet" to populate with the values found in column "C" on the "Data sheet"...ONLY IF the values in Data Sheet (B:B) and Upload Sheet (C:C) match AS WELL AS Data Sheet (D:D) and Upload Sheet (B:B) match. Sorry, I could have organized the columns more alphabetically but I figure you'd get the gist of it and I can play around with the figures. In Upload sheet E2 and pulling formula down, I would put... XLOOKUP(1, ('Data sheet'!D$2:D$7=B2)*('Data sheet'!B$2:B$7=C2), 'Data sheet'!C$2:C$7, "" ) submitted by /u/jwnsfw [link] [comments]
- What formula should I be using to populate one column’s data automatically based on my entry for another column?Hi! I need some help here. first off, I’m not allowed to use a v-lookup for this file due to some organisational restrictions, so i’m unable to create a table 2 to facilitate the lookup values. I’m only allowed to use table 1 itself, which is where I have to put in all my values. I have been trying to use index/match instead but can’t see to figure the right formula out. so basically, what I need is for my manual input of data in column J to populate the associated remark in column K. • column J input “NA” = column K input “visit 2” • column J input “nil” = column K input “insufficient blood” • column J input “1” = column K input blank these are the only 3 that I require. let’s say I require it for 30 rows. so my range of rows to populate would be K2:K30 based on the directly adjacent data of J2:J30. how can I do this in formula or automation? thanks in advance! submitted by /u/yanny-jo [link] [comments]
- I am averaging multiple rows and not all rows have data. I want to apply that result to all rows. Picture attached.Callouts need to be calculated with the formula I have in G2 for each set of data in the data column to its respective callout. Simple Volume Weighting. I need a formula for G where I can drag it down and it look like what I have in Column H. In short, I need Column G to look like how I populated H based on a formula. I am hung up on the order as in there are values low in the rows that need to be applied for the whole callout. I’m over thinking it. Solutions?? https://imgur.com/a/Gcz4Dd7 submitted by /u/networkfound [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#Excel compatibility#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions#AI formula generation techniques#rows.com#formula generator#financial modeling with spreadsheets#XLOOKUP#LAMBDA