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.
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.
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.
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))