IF function involving VLOOKUP

[Giggly4g]

Happy New Year! I want to set up an IF argument that checks to see if a cell
on a different worksheet has an entry from a dropdown list (an entry is not
required). If there is an entry, I want it to populate into the cell that I'm
putting the argument into. If there is not an entry, I want the cell to
populate using a VLOOKUP that is already set up and functioning.

This is what I came up with but I'm getting a #VALUE! error.

=IF('Ants'!$E$2,VLOOKUP('Ants'!$D$1,'Area
Lookup'!$A$2:$C$148,0,1),'Ants'!$E$2)

The sheet this is going on is called Summary, the VLOOKUP table is on Area
Lookup, and the "information" sheet is Ants. Thank you so much for your
help!!!

 

[Dave O]

The vlookup portion of your formula is
VLOOKUP('Ants'!$D$1,'Area Lookup'!$A$2:$C$148,0,1)

The flaw is in the ",0," portion of the formula. That tells Excel
which numeric column of the array to return, and 0 is not permissible:
since your array is from A to C, the possible entries for that
argument are 1, 2, or 3.

The ",1)" portion of the formula indicates whether Excel should return
an exact match for your lookup value or if an inexact return is
allowed. Permissible entries there are 0 or TRUE and 1 or FALSE. Is
that what you intended?

Dave O
Eschew obfuscation

 

[Dave Peterson]

Just a note about that last portion...

0 is the same as False
and
1 is the same as true

 

[Giggly4g]

Hmmm...I copied the VLOOKUP directly from the cell that it was working in
prior to trying to complicate my life. I used the 0,2 to tell Excel to pull
the data in the same row and over one column. If I understand you correctly,
this formula will not work within an IF statement because the mechanics of
the argument have altered the meaning of the 0,2 functionality.

So, here's my next thought. Maybe I should set up one column that pulls the
VLOOKUP values, another column that pulls the dropdown list information (if
entered), that write an IF that evaluates the 2nd column to see if it has an
entry. If so, use it in the third column. If not, use the entry in the first
column. Does that make sense? If so, how do I tell Excel to look for a blank
cell as false?

 

[Dave Peterson]

It doesn't have anything to do with your =vlookup() formula being nested in an
IF statement. The syntax for your =vlookup() isn't right.

If I understand your original question correctly, I _think_ you want something
like:

=IF(Ants!$E$2<>"",Ants!$e$2,
VLOOKUP(Ants!$D$1,'Area Lookup'!$A$2:$C$148,1or2or3,False_or_True))

I don't think you copied and pasted the original formula into your post. If
this suggestion doesn't help, you may want to reply with the exact formula--copy
directly from the formula bar and paste into your post.

 

[Giggly4g]

Dave P. and Dave O...

Thank you so much for your help. Although the suggested formula didn't work,
it did spin me off in the correct direction. I ended up scrapping the VLOOKUP
embedding and just referred to a column containing the VLOOKUP results. This,
along with some rearranging, apostrophes, and laughter seems to have done the
trick.

=IF('Ants'!$D$2<>"",'Ants'!$D$2,'SGM Summary'!$E14)

is what I ended up with and it works like a charm. Since I've got 173 of
these lovely individual worksheets, I'm really excited!

Thank you again for this particular round of support and for everything that
the MVPs have helped me figure out. Have a great year!!!

 

[Dave Peterson]

Just a curiousity question about the apostrophes around the Ants worksheet
reference.

Do they actually show up in the formulabar after you enter the formula?

They disappear for me in xl2003.

Dave P. and Dave O...

Thank you so much for your help. Although the suggested formula didn't work,
it did spin me off in the correct direction. I ended up scrapping the VLOOKUP
embedding and just referred to a column containing the VLOOKUP results. This,
along with some rearranging, apostrophes, and laughter seems to have done the
trick.

=IF('Ants'!$D$2<>"",'Ants'!$D$2,'SGM Summary'!$E14)

is what I ended up with and it works like a charm. Since I've got 173 of
these lovely individual worksheets, I'm really excited!

Thank you again for this particular round of support and for everything that
the MVPs have helped me figure out. Have a great year!!!

 

[Giggly4g]

Yes, the apostrophes appear that way in the formula bar. If memory serves,
that was one of the tweaks I made last night to make the formula work.
Something I was trying to do wouldn't work without them...I think it was
swapping out names for each of the worksheets, some of which are more than
one word. Excel didn't like the multi-word names unless I used apostrophes.

 

[Dave Peterson]

I understand the apostrophes around the worksheet names with spaces (or names
that are numbers or names that look like addresses), but I don't need the
apostrophes for Ants in xl2003.

Yes, the apostrophes appear that way in the formula bar. If memory serves,
that was one of the tweaks I made last night to make the formula work.
Something I was trying to do wouldn't work without them...I think it was
swapping out names for each of the worksheets, some of which are more than
one word. Excel didn't like the multi-word names unless I used apostrophes.