Forum Discussion
Solved
VLOOKUPS with new columns inserted
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 pu...
- 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.
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 🙂
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.- 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))