none
Two lookups different columns to return value

    General discussion

  • Hi there,

    I was trying to apply a formula to populate data by lookup to 2 columns, need help on formula.

    If value matched column Q in reference sheet, then return with value 0.5

    If value matched column S in reference sheet, then return with value 0.75

    If value not matched with both column Q & column S, then return with value 1

    The formula as below:

     Range("J" & lStart).Value = "=IF(AND(ISERROR(VLOOKUP(I" & lStart & ",Reference!$Q$2:$Q$" & Worksheets("Reference").Range("Q2").End(xlDown).Row & ",0.5,0),ISERROR(VLOOKUP(I" & lStart & ",Reference!$S$2:$S$" & Worksheets("Reference").Range("S2").End(xlDown).Row & ",0.75,0),1,0))"
            Range("J" & lStart).Value = Range("J" & lStart).Value

    What's wrong with the setup?

    Thanks!

    Thursday, April 27, 2017 6:13 AM