VLOOKUP formula not working. HELP!

[japc90]

The below formula is not working. When I used the same formula to pull
the values from cells 2-5 of the accompanying spreadsheets it worked
perfectly. But for cells 6-7 I get "#VALUE!."

Any ideas?

=VLOOKUP($A$5,FIRE!$A$4:$O$30,6,FALSE)+VLOOKUP($A$5,AUTO!$A$4:$O
$30,6,FALSE)

Thank you in advance.

 

[Dave Peterson]

I'd guess that one of those functions was returning text--not a number.

If you put each formula in separate cells, what do you get returned from each.

 

[japc90]

I'd guess that one of those functions was returning text--not a number.

If you put each formula in separate cells, what do you get returned from each.

No text. If I separate the formula and put it in different cells it
works. But combined it works about 3/4s of the time. It worked after
cells 6&7 but then not for cell 10.

Thanks!

 

[japc90]

No text. If I separate the formula and put it in different cells it
works. But combined it works about 3/4s of the time. It worked after
cells 6&7 but then not for cell 10.

Thanks!- Hide quoted text -

- Show quoted text -

I think I figured out the problem but not the solution.

Problem: I am using the VLOOKUP to pull data from two separate
spreadsheets within one workbook. I will update these spreadsheets
everyday from a database. Sometimes a person listed on the main
spreadsheet (where the VLOOKUP formula resides) will have data on both
spreadsheets, sometimes their name will only appear on one and
sometimes their name will appear on both but one will have zeros.

I need a formula to cover all of these potential scenarios. I tried:
=IF(VLOOKUP(A7,FIRE!$A$4:$O$30,2,FALSE)=0,0,VLOOKUP(A7,FIRE!$A$4:$O
$30,2,FALSE)+IF(VLOOKUP(A7,AUTO!$A$4:$O
$29,2,FALSE)=0,0,VLOOKUP(A7,AUTO!$A$4:$O$29,2,FALSE))). Didn't work.

Any suggestions?

Thanks!

 

[Dave Peterson]

You could look for a match in portion of the formula:

=if(isna(vlookup(...)),0,vlookup(....))
+if(isna(vlookup(...)),0,vlookup(....))

The top line will do the Fire worksheet. The bottom line will do the Auto
worksheet.