•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Need a formula to match two columns and another 2 columns to return a value
Our take
Are you struggling to match data across two sheets while ensuring accuracy? If you need to align two names and two cities to retrieve the correct ID, you’re not alone. Thankfully, there’s a solution that enhances your current formula. By incorporating a second match for cities, you can eliminate duplicates and pinpoint the right ID with confidence. This introduction will guide you through the process, providing the formula adjustments necessary to achieve precise matches using both criteria. Let’s transform your data management approach today!
For the data I have to match up two names and two cities. If they match then they should return a value of the correct ID. The name, city, and ID that are being matched up to live in another sheet.
So, if Column A (sheet 1)= Column A(sheet 2) and Column B(sheet 1) = Column D(sheet 2) then they'll return the correct ID from column E in sheet 2.
Right now I have a formula two match the two names and return a value, but some of the names are repeats so I need a second match item to get the correct ID.
This is my current formula to match up just the names and return the ID.
=INDEX('Sheet 2'!B:B,MATCH(TRUE,EXACT(O6,'Sheet 2'!A:A),0))
[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]
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Matching Numeric Data across two columns?What I thought would be a simple formula has taken me over 2 hours of searching and no results as yet.. So im reaching out for help please. Heres where Im at. - Using 16.108.2 version - I have 2 sets of data of differing row amounts. There is one numeric column in each set that definitely has matching numbers across them. I need to see which numbers are matching and which numbers aren't. - I need to align those 2 columns where the numbers are matching, and also keep all other information aligned across the columns that are alongside the 2 I am matching. I have tried the XLOOKUP formulas that I can see listed when i do a google search, but i cant get it to work, either some information or step is being left out, or I am finding the wrong info on how to do this. Can anyone provide a step by step on this please? submitted by /u/Sentallmentall [link] [comments]
- checking 2 cells against value of 3 cellsSheet 1 Sheet 2 I'm trying to compare 3 cells value against 2 value and return true/false. I'm able to compare 2 cells, but I'm not sure how do i edit the formula so that Sheet 1 Cell A3, B2 must match Sheet 2 cell A2 and Sheet 1 Cell B3 match Sheet 2 Cell A3 Formula to compare 2 cells =IF(A3&B3=Sheet2!A2&Sheet2!A3,TRUE,FALSE) submitted by /u/rainbow1112 [link] [comments]
Tagged with
#Excel alternatives for data analysis#AI formula generation techniques#generative AI for data analysis#rows.com#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#Excel compatibility#real-time data collaboration#intelligent data visualization#formula#match#columns#ID#names#cities#value#data#sheet#correct