Fixing inconsistent results in INDEX-MATCH formula when lookup values repeat
Our take
Hi everyone, I’m working with a dataset where I need to return a price based on both Product and Region, but my current INDEX+MATCH formula only matches the first occurrence and gives the wrong result when values repeat; for example, with data like Apple-East-10, Apple-West-12, Orange-East-8, Orange-West-9, when my lookup inputs are Product = Apple and Region = West, my formula =INDEX(C2:C5,MATCH(A9,A2:A5,0)) returns 10 instead of 12 because it only checks the first match; so far I’ve tried adding a helper column combining Product and Region, experimenting with nested MATCH, and attempting XLOOKUP, and I also reviewed a detailed Excel functions guide online to better understand lookup logic and why this happens (spreadsheetpoint), but I still haven’t found a clean formula solution that handles multiple criteria without helper columns, so I’m looking for a correct formula approach that will return 12 for this case.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Creating a complex index match formulaExcel 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) submitted by /u/Sorry_Background8898 [link] [comments]
- 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]
- Index Match or Xlookup to return sum of row values based on matching two horizontal lookups?Ask: If E59:S59=E111 AND E60:s:60<-E112, give me the sum of E61:71,else return 0 What is the smoothest formula for this? I added in what I'm trying to do on the formula line. ChatGPT is less helpful with this than I am. I want to bundle by phase because our timesheet system doesn't allow me to pull a report by phase/task, only by person. I tried Xlookup but kept getting SPILL errors so I am at a loss. FTE1 is a different category and should not be included (PM, I can plug that as it's always in the same position). Thanks in advance for your help. https://preview.redd.it/yp4yzfn2rcxg1.png?width=2560&format=png&auto=webp&s=86eed1d2c8f3bb97c6d49592a0b1978d5f80f365 submitted by /u/Regular_or_BQ [link] [comments]