Changing a IF(VLOOKUP) into VLOOKUP

Hi all,

I am currently using this formula:

=IF(VLOOKUP(E$9,Tabelle2!$A$2:$AP$5500,ROW($A2),TRUE)="","",VLOOKUP(E$9,Tabelle2!$A$2:$AP$5500,ROW($A2),TRUE))

Can this be changed to a VLOOKUP formula and keep the same results.

As always, You help is greatly appreciated

regards

Jeremy

VLOOKUP will return error if no matching is found. You can mask the error using IFERROR like below

Formula:

=IFERROR(VLOOKUP(E$9,Tabelle2!$A$2:$AP$5500,ROW($A2),TRUE),"")

Hi and Thank You Both,

When i put this in my VBA script and run it, it fill the cells ok but then comes up with the following err.

My Code:

Dim rng As Range
    Dim R As Integer
    Dim C As Integer
    Set rng = Range("E10")  'Range
    rng.Select
    
Application.ScreenUpdating = False

For R = 1 To 40 'loop rows,
    rng.Offset(R, 0).Select
    Selection.Cells.FormulaR1C1 = "=IFERROR(VLOOKUP(R9C,Tabelle2!R2C1:R5500C42,ROW(R[-8]C1),TRUE),"")"
        For C = 1 To 47 'loop columns,
        rng.Offset(R, C).Select
        Selection.Cells.FormulaR1C1 = "=IFERROR(VLOOKUP(R9C,Tabelle2!R2C1:R5500C42,ROW(R[-8]C1),TRUE),"")"
    Next C
Next R

Application.ScreenUpdating = True

The Error i get is:

Laufzeitfehler 1004
Anwendungs- oder objektdefinierter Fehler

Any Ideas

JRidge, you should have mentioned that you are going to use this in VBA

ActiveCell.Formula = "=IFERROR(VLOOKUP(R9C,Tabelle2!R2C1:R5500C42,ROW(R[-8]C1),TRUE)," & Chr(34) & Chr(34) & ")"

I get blanks bud. Can you share your workbook?

JRidge, as I said VLOOKUP will return error only if it could not find what you are looking for. In your workbook, the date you are looking for is found and VLOOKUP returned the 3rd column value which is 0. No problem with the formula.

If you want the 0s to blank. You have to use IF.