Hi all, I am trying to incorporate LOOKUP formula and had few questions:
1) The current formula I have utalizes 2 variables, could this be expanded to 3?
"=LOOKUP(9^99,IF({1,0},0,LOOKUP(2,1/(A2=Previous Day!A$8:A$" & LRDat2 & ")/(H2=Previous Day!H$8:H$" & LRDat2 & "),Previous Day!M$8:M$" & LRDat2 & ")))"2)
the current formula I have seems to return numbers only? as the text that I want to be returned gets returned as 0
3) finally, I would like to incorporate it so the formula skipps any non blanks. I have below that I use, but everytime I tried to incorporate this, it wouldnt work..I have bits and pieces of everything and was looking for some help to piece it together.. when i try to run below I get Compile error "Invalid or unqualified Reference" under below red text
Sub test()
Windows("Summary.xls").Activate
Sheets("data").Select
LastRow2 = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
LRDat2 = Sheets("Previous Day").Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range("M2:M" & LastRow2).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.Formula = _
"=LOOKUP(9^99,IF({1,0},0,LOOKUP(2,1/(A2=Previous Day!A$8:A$" & LRDat2 & ")/(H2=Previous Day!H$8:H$" & LRDat2 & "),Previous Day!M$8:M$" & LRDat2 & ")))"
Set rng = .Range("M2:M" & LastRow2)
rng.Value = rng.Value
End Sub
Bookmarks