none
Is there a way to simply my vlookup equation? RRS feed

  • Question

  • =IF(ISNA(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,3,FALSE))," ",VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,3,FALSE))&" "&IF(ISNA(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,6,FALSE))," ",VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,6,FALSE))&"  "&IF(ISNA(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,9,FALSE))," ",VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,9,FALSE))&" "&IF(ISNA(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,5,FALSE))," ",VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,5,FALSE))&"  "&IF(ISNA(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,7,FALSE))," ",VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,7,FALSE))&"  "&IF(ISNA(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,8,FALSE))," ",VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,8,FALSE))

    Seems to me there should be a smarter way to implement this. Please advise. I have one list of data, but need to extract multiple cells to populate a calendar cell.

    thanks you


    heads up

    Saturday, November 23, 2013 2:08 AM

All replies

  • This is not a VBA question as far as I can see, but an Excel question.

    In all versions of Excel, you can use

    =IF(ISNA(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,3,FALSE)), "", VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,3,FALSE)) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,6,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,9,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,5,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,7,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,8,FALSE))

    In Excel 2007 and later, you can use

    =IFERROR(VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,3,FALSE)) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,6,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,9,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,5,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,7,FALSE) & " " & VLOOKUP(E4,'WIP_A.xlsm'!TaskRange,8,FALSE), "")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 23, 2013 10:44 AM
  • Doh! Your right wrong forum... It feeds vba thou. Thank you Kind Sir!

    heads up

    Saturday, November 23, 2013 9:11 PM