SOLVED

VLOOKUPS with new columns inserted

Contributor

Can someone help me understand how to perform a VLOOKUP on new columns that are inserted into a table? 

I want to insert new date columns in between BP and BQ, so when the vlookup is performed it pulls through the last three columns. When a new one is inserted, this will need to be considered as the latest three? 

Thanks

9 Replies
It works better if you post a sanitized version of your workbook with just the relevant pieces in it. I'd say you might better use something like the new XLOOKUP function, or a combination of INDEX and two MATCH functions.
Hi Jan, would you able to provide an example formula please?
Sure, if you provide a sample sheet

Thanks @Jan Karel Pieterse 
So when I add a new column between column L and M on sheet 2, the table within sheet 1 automatically pulls the latest 3 columns highlighted yellow (columns D - I on sheet 1).

 

Then when a column is inserted after column Q on sheet 2, the 3 latest columns are pulled through to the table within sheet 1 (columns J-L in sheet 1).

 

Please find sample data attached.
Thanks.

 

@milo1234 I think you asked for the attached, but I'm not 100% sure. Please note that this assumes you are using Excel 365.

Hi Jan, thank you for providing the example formulas.
Can the date column headings within sheet 2 not be merged? Will this cause a problem?
Also, the dates on sheet 1 will need to change to pick up the latest 3 columns on sheet 2.
Please could you help me with this?

Thank you
Merged cells are never a very good idea in Excel, it simply doesn't work well with them.

If you place this formula into cell K1 on Sheet1 (make sure to clear the cells to the right of this formula) you should get the latest three dates:
=LARGE(Sheet2!N2:S2,{3,2,1})
Make sure you insert new columns before column S on Sheet2 for this to keep working.
best response confirmed by milo1234 (Contributor)
Solution
Hi Jan,

Thanks this works - but I will be adding columns after column R on sheet 2 continuously. Will this affect the date?

Also, I want the dates in columns D-I on sheet 1 to automatically pick up the last 3 date columns too.
You should be fine if you remember to :
- Insert new columns between immediately after the last column on Sheet2 (currently between column R and S)
- Adapt the formula in Sheet1K3 to:
=INDEX(Sheet2!N4:S6,MATCH(A3:A5,Sheet2!A4:A6,0),MATCH(K1:M1,Sheet2!N2:S2,0))