Forum Discussion
Solved
Integrating subset of Excel workbook with original workbook
I searched prior posts and have not found a solution to my issue but if someone can help or direct me to other help I appreciate it.
I have two Excel workbooks, one with over 3000 rows (I'll call it "A") and the other with just over 1100 rows ("B"). The second is a subset of the first. To select the B subset, I copied just the first few columns from A because those helped me categorize cases as meeting/not meeting certain criteria. Foolishly I deleted the cases from B that were not of interest so the first column with identifying numbers does not match between A and B.
But now I need to match some of the other columns from A with the selected cases that make B. The first column consists of a code number - "IncidentID" that can be used to match. But the occurrence of selected cases is irregular. I show an illustration of A and B below. The highlighted cases are the ones that match in this sample - so everything of B is of interest but I need to match these with only some of the cases in A, and again these are not regularly spaced. A actually has far more columns than I show but I only need about 3 of them integrated with B. If I could somehow add the new column - TECH Code - to A in the proper spaces, then I could delete the ones that had blanks there. I assume there is something I could do with another program - R or Python, but I am pretty limited in my knowledge of either of those for data management. I appreciate any help and will do my best to provide more details if asked. I use Excel for Mac.
- What you need is the XLOOKUP function. Open both workbooks next to each other.
- On an empty cell next to your data, type =XLOOKUP(
- Point at the cell in column A on the same row, you should now have something like =XLOOKUP(A2
- press the comma on your keyboard
- Now select the cells in column A of the other workbook. Your formula will look like this:
=XLOOKUP(A2,[Book1]Sheet1!$A$2:$A$3500
- press the comma again and point to the cells from which you need to pull information.
- press enter
your formula should look similar to this:
=XLOOKUP(A2,[Book1]Sheet1!$A$2:$A$3500,[Book1]Sheet1!$D$2:$D$3500)
- What you need is the XLOOKUP function. Open both workbooks next to each other.
- On an empty cell next to your data, type =XLOOKUP(
- Point at the cell in column A on the same row, you should now have something like =XLOOKUP(A2
- press the comma on your keyboard
- Now select the cells in column A of the other workbook. Your formula will look like this:
=XLOOKUP(A2,[Book1]Sheet1!$A$2:$A$3500
- press the comma again and point to the cells from which you need to pull information.
- press enter
your formula should look similar to this:
=XLOOKUP(A2,[Book1]Sheet1!$A$2:$A$3500,[Book1]Sheet1!$D$2:$D$3500)- Thanks so much; this is great. I forgot to copy it down the row the first time, and walked away from this for a few hours. Then I thought about how Excel works and realized my error. After that this was quick and simple. I also looked at a little more information about XLOOKUP and found I could return a custom value for no match if I wanted to go to the trouble to do so. But I can sort on the #N/A values easily enough and categorize the other variables of interest for the selected cases. Thanks again for your help!
