Find Excel three-columns match in two different sheets?

Question

I've three sheets from differents periods of several companies It happens that not same accounts were used on all periods. I need to compare two or three companies from two different periods. I've managed to pull out the data from any company on another sheet using HLOOKUP and works like a charm, **what I need is to populate also the account name field.** I was thinking that probably and IF formula could work, something like if that cell = 0 then populate nothing but if ≠ 0 then populate the account field name But on sheets not all account are on same column. its like - 5701 · Construction Supplies    - 57011 · Construction Supplies - Other - Total 5701 · Construction Supplies Please note that for comparison purpose I need same accounts on same rows so if second company didnt used - 57011 · Construction Supplies - Other then when populate should be like 5701 · Construction Supplies    (Blank row) Total 5701 · Construction Supplies So, how could i do this? thanks in advance

Answer 1

You could certainly use HLOOKUP or VLOOKUP approaches to solve a challenge like this, but in the long run, it won't be particularly stable or sustainable, especially if anyone else ever accesses or edits these files.

A longer term solution would be to use PowerQuery in Excel and identify a common key (a column that holds a data point that all three data sets will have in common) to merge data across tables regardless of new additions. This also works well if you perform the same or similar queries across different data sets.

For the challenge you name of the account names being in different columns, you can also use PowerQuery to create an array and then find the position/column number of the value in question.

https://docs.microsoft.com/en-us/powerquery-m/list-positionof

These are just some thoughts to get started with!