Forum Discussion
VLookUp and IFs
I have a code that I look up in one sheet and when it is find I need to put in a forecast date that is in another column, however, if that forecast date cell is empty then I need Excel to go to the next column and pick a cell in the other date cell, if that cell is blank then choose another column with a date.
Example
Code is in Cell A1 - VLookUp looks for that cell.
In the other sheet there are 3 columns that may contain dates
If the vlookup finds the code then I need it to look in column m and if there is a date in the cell in column m return that date, if there is no date in that M cell, then move to Column K, if there is a date then in column K then use that date, if there is no date in column K then go to column D and use whatever date is in the C column.
Any help would be greatly appreciated.
Thanks
For example:
=IFERROR(IF(VLOOKUP(A1,OtherSheet!$A$1:$M$100,13,FALSE)<>"", VLOOKUP(A1,OtherSheet!$A$1:$M$100,13,FALSE), IF(VLOOKUP(A1,OtherSheet!$A$1:$M$100,11,FALSE)<>"", VLOOKUP(A1,OtherSheet!$A$1:$M$100,11,FALSE), IF(VLOOKUP(A1,OtherSheet!$A$1:$M$100,4,FALSE)<>"", VLOOKUP(A1,OtherSheet!$A$1:$M$100,4,FALSE), ""))), "")
Where OtherSheet is the name of the sheet with the lookup range. Adjust the last row 100 if necessary.
4 Replies
For example:
=IFERROR(IF(VLOOKUP(A1,OtherSheet!$A$1:$M$100,13,FALSE)<>"", VLOOKUP(A1,OtherSheet!$A$1:$M$100,13,FALSE), IF(VLOOKUP(A1,OtherSheet!$A$1:$M$100,11,FALSE)<>"", VLOOKUP(A1,OtherSheet!$A$1:$M$100,11,FALSE), IF(VLOOKUP(A1,OtherSheet!$A$1:$M$100,4,FALSE)<>"", VLOOKUP(A1,OtherSheet!$A$1:$M$100,4,FALSE), ""))), "")
Where OtherSheet is the name of the sheet with the lookup range. Adjust the last row 100 if necessary.
- Hi Hans that was my mistake - it is working perfectly thank you so much !!
Kind regards
Ann - Hi Hans
I tested this and it works for column 13 but when I remove the date in column 13 I was hoping it would pick up the April date in column 11 but it went straight to the date in column D. - Hi Hans
Thank you I will try this and respond !
Much appreciated 🙂
Ann
