•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Creating a complex index match formula
Our take
Creating a complex index match formula can be a challenging yet rewarding task. In your scenario, you're looking to match a name and type on one sheet with corresponding price data on another. This involves setting up a formula that accurately references both the names and types, ensuring that each has its distinct price point. With two types per name, clarity in your formula will be key to obtaining the correct price.
Excel version: 2602, Microsoft 365
Hey everyone,
I am currently creating a kinda complex (at least for me) index matching formula and would like some opinions on how to flush this out as it’s been a while since I’ve done one like this.
I need to input data (price) into a cell based on data (a name) in a cell on the same sheet and compare that name to a price point on another sheet based on another set of information which is also available on both sheets.
So it would basically be lookup based on name and type to input a price based on name and type from another sheet. But there’s two “types” per name and each also have a different price point…. Hope this all makes sense 🫠
Any advice would be wonderful, thanks!
(Photo in comments)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Hash operators within Index/MatchIntermediate-Advanced user on (Office 365 - Windows 11). My workbook is mostly developed in Excel desktop, and the live co-authored workbook is hosted on SharePoint. I have an issue running hash-operators within an INDEX/MATCH formula. The resulting value does not align with the formula evaluation conclusion. As a hypothetical example about what's happening: Col A contains a spilled array listing unique identifiers, Col B and Col C contain spilled arrays of data, and Col D contains an Index/Match formula. The formula is rooted in Cell D2, and spills down equal with the other columns (preceding it with [ =IF(B2#="","", ] forces it to spill). The formula finds the value of Col A based on the always-unique permutations resulting from searching B2# and C2# together. In this example: D2 should find A6, D3 should find A5, and D4 should find A7. However, all results mirror the first answer. I.e. D2, D3, and D4, all point to A6. There are no formula errors, and the formula evaluator shows everything working properly. For example, evaluation of D3 (even though it is spilled) shows that it processes perfectly, beginning to end. D3 evaluation concludes by correctly identifying the A5 value. However, the output on the sheet shows the A6 value, directly conflicting with the formula evaluator. Any insights would be greatly appreciated. submitted by /u/Surge_x [link] [comments]
- Excel Performance optimisation tips!Working in demand planning I have got it the point where I am making some pretty advanced files using a suite of techniques. My files often have lots of rows, with lots of Columns of complex formula including with sumifs, xloopup, ifs & Let. I’ve not advanced to using tables regularly though as I find the constraints & syntax annoying but am trying to get there & have started using power query to blend data for output analysis. The problem I am encountering is I filter ALOT drilling down into product groups etc, & excel tends to ‘hang’ a lot with ‘Not Responding’. Now I’m not sure it’s due to an underpowered machine (intel core i7 HP Elitebook) or, more likely lots of complex formula referencing ranges or tables. My question to the hive brain: share your optimisation tips & tricks! -Can Lamda combined with Let speed things up? -Are Tables vital to speeding up complex sumifs & lookups? - are match helper columns combined with Index leaner & faster than xlookup? Hit me with best tips & tricks! submitted by /u/NZGRAVELDAD [link] [comments]
- This is probably the most complicated Excel formula I’ve ever seen.I came across this formula in a file at work and honestly… I just sat there staring at it for a good minute. It works, but trying to actually understand what it’s doing is a whole different story. Here’s the formula: =IFERROR(INDEX($B$2:$B$100, MATCH(1, (IF($D$2="All",1,($C$2:$C$100=$D$2))) * ($A$2:$A$100=MAX(IF($D$2="All",$A$2:$A$100,IF($C$2:$C$100=$D$2,$A$2:$A$100)))), 0)), "No Result"). I can kind of follow pieces of it, but the whole thing together feels like someone just kept stacking functions until it magically worked. Is this normal for advanced Excel users or is this more of a “don’t touch it if it works” situation. Also curious — would you rewrite this using something like XLOOKUP / FILTER, or just leave it alone. Would really appreciate if someone could explain this in plain English. submitted by /u/Kindly-Meaning9112 [link] [comments]
- How do I create dynamic dropdowns that works 2 ways?I have an Order Form sheet set up with a fair bit of code in the background. Part of the Form has order lines with headers of Code, Category and Product Description. I have a separate Products list which I wanted to create the dynamic drop down lists using formulas. I soon found out that you cannot type a FILTER formula directly into the Excel Data Validation source box. After a bit of researching, I figured how to to create dynamic dropdowns by referencing a spill range. The way I've set it up is probably a bit messy (but it works). At the moment the user has to select the category of product they want first. Then the drop down list in Product Description will show only items in that category. When they select the item they want, the drop down list for Code will show the specific code that item relates to. It can work but I was hoping to be able to use partial searches in any of the 3 columns to generate suggestions. eg. Start typing partial item name which would then narrow down options in the drop down list for Products as you type each letter. Select the item and the category and Item Code self populates. Also enable the user to partially type item code or category for a similar result. Hope this makes sense. And if so, any ideas? submitted by /u/AdComplete9707 [link] [comments]
Tagged with
#Excel alternatives for data analysis#cloud-based spreadsheet applications#generative AI for data analysis#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#natural language processing for spreadsheets#google sheets#Excel