software

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 quot;winnerquot; 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: www.excelforum.com/attachment.php?postid=4788 |
-------------------------------------------------------------------

--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile: www.excelforum.com/member.php...oamp;userid=32352
View this thread: 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=543500
daddylonglegs Wrote:
gt; Instead of =VLOOKUP(a1,b1:c2,2,0)
gt;
gt; use
gt;
gt; =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: www.excelforum.com/member.php...oamp;userid=32352
View this thread: 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: www.excelforum.com/member.php...oamp;userid=32352
View this thread: 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=543500
daddylonglegs Wrote:
gt; I can't tell from your attachment what formula you're using but you
gt; should be able to make the references to the lookup range absolute by
gt; 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: www.excelforum.com/member.php...oamp;userid=32352
View this thread: www.excelforum.com/showthread...hreadid=543500
I don't know if your serious or not because of the you keep going
lol

Do you still need help???--
davesexcel------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=543500
davesexcel Wrote:
gt; I don't know if your serious or not because of the you keep going
gt; lol
gt;
gt; Do you still need help???
YES, I will always need help with excel...LOL.... I am forever excel
challenged...lol--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile: www.excelforum.com/member.php...oamp;userid=32352
View this thread: www.excelforum.com/showthread...hreadid=543500
Hi,

re duplication of the vlookup formula:
DaddyLongLegs' suggestion of using dollar signs seems to be what you
need. Using dollar signs makes a reference quot;absolutequot; rather than
quot;relativequot; ie it doesn't change - check out Excel Help for more detail
[F1].

To overcome the quot;#N/Aquot; problem after widening column H and half the
number of times a vlookup is performed since you have more spreadsheets
to copy your formula into I would change DaddyLongLegs sugestion of (as
per your layout):
=IF(ISNA(VLOOKUP(G6,$D$6:$E$100,2,FALSE)),0,VLOOKU P(G6,$D$6:$E$100,2,FALSE))
from above by inserting another column before column H entering
=VLOOKUP(G6,$D$6:$E$100,2,FALSE)
into the new column H (this can column can be hidden later). Then enter

=IF(ISNA(H6),0,H6)
into the quot;Ptsquot; column (ie the old column H, now column I).

quot;There has to be a shortcut for this...quot;
Yes, there is, repeat the column insertion to the left of each set of
lookups.
With the references for the lookup range now being quot;lockedquot; to columns
D amp; E, you should be able to select the 2 cells H6 amp; I6, copy them,
paste them down the rows needed amp; the same in the new columns across
the page.
Now the new columns can be hidden.hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=543500
broro183 Wrote:
gt; Hi,
gt;
gt; re duplication of the vlookup formula:
gt; DaddyLongLegs' suggestion of using dollar signs seems to be what you
gt; need. Using dollar signs makes a reference quot;absolutequot; rather than
gt; quot;relativequot; ie it doesn't change - check out Excel Help for more detail
gt; [F1].
gt;
gt; To overcome the quot;#N/Aquot; problem after widening column H and half the
gt; number of times a vlookup is performed since you have more spreadsheets
gt; to copy your formula into I would change DaddyLongLegs sugestion of (as
gt; per your layout):
gt; =IF(ISNA(VLOOKUP(G6,$D$6:$E$100,2,FALSE)),0,VLOOKU P(G6,$D$6:$E$100,2,FALSE))
gt; from above by inserting another column before column H entering
gt; =VLOOKUP(G6,$D$6:$E$100,2,FALSE)
gt; into the new column H (this can column can be hidden later). Then enter
gt;
gt; =IF(ISNA(H6),0,H6)
gt; into the quot;Ptsquot; column (ie the old column H, now column I).
gt;
gt; quot;There has to be a shortcut for this...quot;
gt; Yes, there is, repeat the column insertion to the left of each set of
gt; lookups.
gt; With the references for the lookup range now being quot;lockedquot; to columns
gt; D amp; E, you should be able to select the 2 cells H6 amp; I6, copy them,
gt; paste them down the rows needed amp; the same in the new columns across
gt; the page.
gt; Now the new columns can be hidden.
gt;
gt;
gt; hth
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...

Thanks for the response. This has helped but has lead to other
questions..lol.

This formula that you gave me:
=IF(ISNA(VLOOKUP(G6,$D$6:$E$100,2,FALSE)),0,VLOOKU
P(G6,$D$6:$E$100,2,FALSE)) Where do I insert this? based on my
spreadsheet?? I have added the new column H, but i find when using the
above formula it works in some cells but not in others. I can change
the first and last G cell to match where i want it to pull but i don't
always get the points value that is assigned to the referencing pts.
column. I will get ZERO regardless of who i put in the field. Any
suggestions...It seems to work on its own, but not in every
cell....very strange...--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile: www.excelforum.com/member.php...oamp;userid=32352
View this thread: www.excelforum.com/showthread...hreadid=543500