VLOOKUP

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

Cell A8 = "03/31/04"

A different workbook has the cost for each day of the
year. If the date is not displayed, then this means that
this particular is not complete. I want to do a VLOOKUP of
if the date matches on the other workbook then type the
cost. This is the formula I have.

=IF(A8>0,VLOOKUP(A8,'[New.xls]Sheet 1'!
A3:G3000,7,FALSE),"")

If this date is not on Sheet 1 then it returns a #N/A. I
want it to be if it = #N/A then "".
 
just do the =vlookup() twice.

simple example:

=if(iserror(vlookup()),"",vlookup())

in your case, I think I'd swap the order (just my own preference):

=if(a8="","",if(iserror(VLOOKUP(A8,'[New.xls]Sheet 1'!A3:G3000,7,FALSE)),"",
VLOOKUP(A8,'[New.xls]Sheet 1'!A3:G3000,7,FALSE)))

(all one cell)
Cell A8 = "03/31/04"

A different workbook has the cost for each day of the
year. If the date is not displayed, then this means that
this particular is not complete. I want to do a VLOOKUP of
if the date matches on the other workbook then type the
cost. This is the formula I have.

=IF(A8>0,VLOOKUP(A8,'[New.xls]Sheet 1'!
A3:G3000,7,FALSE),"")

If this date is not on Sheet 1 then it returns a #N/A. I
want it to be if it = #N/A then "".
Click to expand...
 
One way:

=IF(A8>0,IF(ISNA(MATCH(A8,'[New.xls]Sheet 1'!A3:G3000,FALSE)),"",
VLOOKUP(A8,'[New.xls]Sheet 1'!A3:G3000,7,FALSE)),"")
 
I bet JE meant this:

=IF(A8>0,IF(ISNA(MATCH(A8,'[New.xls]Sheet 1'!A3:A3000,FALSE)),"",
VLOOKUP(A8,'[New.xls]Sheet 1'!A3:G3000,7,FALSE)),"")

(I changed the A3:G3000 to A3:A3000 in the =Match() portion.)

JE said:
One way:

=IF(A8>0,IF(ISNA(MATCH(A8,'[New.xls]Sheet 1'!A3:G3000,FALSE)),"",
VLOOKUP(A8,'[New.xls]Sheet 1'!A3:G3000,7,FALSE)),"")

Troy said:
Cell A8 = "03/31/04"

A different workbook has the cost for each day of the
year. If the date is not displayed, then this means that
this particular is not complete. I want to do a VLOOKUP of
if the date matches on the other workbook then type the
cost. This is the formula I have.

=IF(A8>0,VLOOKUP(A8,'[New.xls]Sheet 1'!
A3:G3000,7,FALSE),"")

If this date is not on Sheet 1 then it returns a #N/A. I
want it to be if it = #N/A then "".
Click to expand...
Click to expand...
 
Back
Top