4788

In the following spreadsheet, I'm trying to get the Vlookup function
to, well, function..lol. I'm trying to get it to put a zero in the
total fields if the "winner" field doesn't match. Excel won't add the
columns unless I get a zero in there. Can someone help me with this???
email me at if you can help...thanks...


+-------------------------------------------------------------------+
|Filename: WENFootballPoll.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4788 |
+-------------------------------------------------------------------+

--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile: http://www.excelforum.com/member.php...o&userid=32352
View this thread: http://www.excelforum.com/showthread...hreadid=543500

Instead of =VLOOKUP(a1,b1:c2,2,0)

use

=IF(ISNA(VLOOKUP(a1,b1:c2,2,0)),0,VLOOKUP(a1,b1:c2 ,2,0))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=543500

daddylonglegs Wrote:
Instead of =VLOOKUP(a1,b1:c2,2,0)

use

=IF(ISNA(VLOOKUP(a1,b1:c2,2,0)),0,VLOOKUP(a1,b1:c2 ,2,0))
Hey, thanks for the response. I am having trouble with this though..
It gave me the zero I wanted, but I'm not proficient enough in excel to
understand how to use this. can you look at my attachment and then pick
a cell and apply the formula to that cell for me, so I can see it
working?? It would be a big help. Thanks..


--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile: http://www.excelforum.com/member.php...o&userid=32352
View this thread: http://www.excelforum.com/showthread...hreadid=543500

Got it working....Now...as you can see, I've got to put this formula in
a TON of places all with different cell criteria... Is there a way to
batch post this formula and have it automatically adjust the formula
for the cell its in? Otherwise its going to be a HUGE undertaking...:(


--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile: http://www.excelforum.com/member.php...o&userid=32352
View this thread: http://www.excelforum.com/showthread...hreadid=543500

I can't tell from your attachment what formula you're using but you
should be able to make the references to the lookup range absolute by
using $ signs, can you post your first VLOOKUP formula?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=543500

daddylonglegs Wrote:
I can't tell from your attachment what formula you're using but you
should be able to make the references to the lookup range absolute by
using $ signs, can you post your first VLOOKUP formula?
Sure, This is what I started with on recommendation from someone
else...

=VLOOKUP(G6,D6:E100,2,FALSE) Obviously G6 is the criteria I want to
match, and D6 is what I want to match it too, and E100 assigns the
point value in the E column. Problem was, if the right team wasn't
selected, i wasn't getting a ZERO in the formula, I was getting ##'s.
Now, I need to use the =IF statement a hundred times or so on the same
page but changing the cells in every one of them. Its going to take
hours to set up. There has to be a shortcut for this..LOL..

Also, I'm going to have to duplicate this formula on a second(and
possibly a third) sheet in the workbook and have it reference back to
the COLUMNS D and E on the first sheet. I don't know how to do that
either..lol...HELP!!!! :)


--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile: http://www.excelforum.com/member.php...o&userid=32352
View this thread: http://www.excelforum.com/showthread...hreadid=543500