Hi there,
I'm an absolute noob on Excel, so apologies if this is a stupid question.
I'm trying to merge 2 tables (on an excel I'm on and on a file called Q4Data)
I could use a VLOOKUP but it's so annoying to manually have to update the column number e.g =VLOOKUP(C2,'[Q4Data.xlsx]Sheet 1'!A:D,4,FALSE) for 20 columns (basically go in each cell to update the 3rd item to 4,5,6,7, etc.) before I drag down everything to fill in the rows (if there's a shortcut here, I'm a taker!)
I thought I'd have better luck with an indexmatch function.
I have this =INDEX('[Q4Data.xlsx]Sheet 1'!$G$2:$AC$169,MATCH(D2,'[Q4Data.xlsx]Sheet 1'!$C:$C,0),MATCH(X1,'[Q4Data.xlsx]Sheet 1'!$G$1:$AC$1,0))
- To drag down I can lock $X$1 (column header) so that D2 updates to D3, D4, etc
- To drag right I can lock $D$2 (client name that I need to match) to that it's X1 that changes to Z1, AA1, etc.
BUT obviously now I can't either drag down or right as the wrong cell is locked.
Is there a shortcut I don't know? OR a better formula altogether?
Keen to learn!
Thank you so much.
Bookmarks