•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
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?
Our take
To efficiently query data between two workbooks in Excel, you can streamline your approach by utilizing a combination of `XLOOKUP` and `IFERROR` functions. Start by creating a single formula for each of the five lookup columns in Workbook 1. Use `XLOOKUP` to search for the "EntID" in both Column A and Column B of Workbook 2. If a match is found, pull the corresponding data; if not, return "Not Found." This method minimizes complexity and enhances performance, making your solution more refined and user-friendly.
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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Two tables/two workbooks. Matching data in columns to add data to a third.I have two different tables on two workbooks. Table 1, Workbook 1: Column 1 is names, Column 2, initials, Column 3 dates. Table 2, Workbook 2: Column 1 initials, Column 2 dates. There is more data on the tables that isn’t relevant to my question and not all names, initials, and dates are common to both tables. Is there a way to match dates and initials on both tables (Columns 1 and 2) that will add the names to a Column 3 on Table 2, Workbook 2? Right now I’m eyeballing dates and initials and cutting and pasting. submitted by /u/TheCliff977 [link] [comments]
- combining two columns into one without vstack or an arrayi have two columns of names on two separate sheets of a workbook. on a third sheet, i want to combine all of the (unique, but that part is easy) names into one column. from there, some vlookups nested in iferrors pull in additional from whichever sheet the name came from (looks for the name in the larger spreadsheet; if it's there, do the vlookup, but if there's an error, check the smaller sheet and do the vlookup). vstack does this nicely, but because it returns an array, i can't use basic sorting anymore. the sort function exists, but we want to be able to do different sorts at different times and this sheet is for use by my less tech savvy coworkers, so i can't rely on that. a recommendation of =query1 & query2 was attempted but i ended up with the first row of each spreadsheet in one cell. please let me know if anyone else has ideas! i've been googling but keep hitting walls :') submitted by /u/nidoqueenofhearts [link] [comments]
- Transfer Data from One Workbook to Another (SPILL Issue)Hey! I’m working on an Excel setup where the workbook on the left is my source and the one on the right is my destination. My goal is to automatically transfer the “Given IDs” from the source to the destination based on their assigned codes. I fixed the one‑to‑many lookup issue, but now I have another problem — when I type an existing code again in the destination (highlighted in green), it shows #REF! instead of its IDs. Is there any way to fix this without losing the automatic transfer I already have? + A user suggested to the COUNTIF into a MOD. I'm not sure how to operate this so I hope anyone could teach me. + "FILTER('[book 2.xlsx]Sheet2'!B:B,ISNUMBER(XMATCH('[book 2.xlsx]Sheet2'!A:A,B:B)))" = this also helps but I also encounter the aforementioned problem (in my real workbooks, there will be times when I need to enter the same codes directly in the destination, and I don’t want to keep going back to the source workbook to re‑enter them all over again.) Thank you so much in advance! https://preview.redd.it/cb9jv0h57ryg1.png?width=1314&format=png&auto=webp&s=b26b1b2886d53f8fe94b76735e4de21ea9a3b866 submitted by /u/jeankrstein [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]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#no-code spreadsheet solutions#AI formula generation techniques#big data management in spreadsheets#conversational data analysis#Excel compatibility#rows.com#real-time data collaboration#intelligent data visualization#data visualization tools#formula generator#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions#Workbook 1#Workbook 2