Forum Discussion
Matching columns in two spreadsheets
This is a job for Power Query in my opinion. In the attached file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table to the right of the blue tables or in another worksheet. Actually it doesn't matter if the tables are in the same sheet or in different sheets.
Thanks for these, but they seem too complicated. I was able to use XMATCH to find the records I wanted (in the larger database, all the records have record numbers in the first column). Now I need to know how to return the results of an IF function so that if the logic test succeeds, the output is to copy the row of cells in the row in which the match occurred.
=XLOOKUP(I2,$A$2:$A$30,$C$2:$G$30)Because you have access to XMATCH you could apply XLOOKUP for the expected results. XLOOKUP works between 2 spreadsheets as well.
Thanks, all for your help. I was able to use XLOOKUP to get what I needed, but I had to do each column separately. I guess I could have figured out how to automate the entire process, but I only needed 9 columns of date, so it just seemed easier to manually change the formula for each column. Here's the formula I used: =XLOOKUP(AE2,$E$2:$E$2344,$A$2:$A$2344). I changed the A to B, C, D, etc. for each column. The AE column had the data I wanted to compare to column E.
