VLookup Function

[AlienBeans]

4788

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

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

 

[daddylonglegs]

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

use

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

 

[AlienBeans]

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]

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...

 

[daddylonglegs]

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?

 

[AlienBeans]

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!!!!

 

[davesexcel]

I don't know if your serious or not because of the you keep going
lol

Do you still need help???

 

[AlienBeans]

I don't know if your serious or not because of the you keep going
lol

Do you still need help???

YES, I will always need help with excel...LOL.... I am forever excel
challenged...lol

 

[broro183]

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 "absolute" rather than
"relative" ie it doesn't change - check out Excel Help for more detail
[F1].

To overcome the "#N/A" 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,VLOOKUP(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 "Pts" column (ie the old column H, now column I).

"There has to be a shortcut for this..."
Yes, there is, repeat the column insertion to the left of each set of
lookups.
With the references for the lookup range now being "locked" to columns
D & E, you should be able to select the 2 cells H6 & I6, copy them,
paste them down the rows needed & the same in the new columns across
the page.
Now the new columns can be hidden.


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[AlienBeans]

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 "absolute" rather than
"relative" ie it doesn't change - check out Excel Help for more detail
[F1].

To overcome the "#N/A" 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,VLOOKUP(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 "Pts" column (ie the old column H, now column I).

"There has to be a shortcut for this..."
Yes, there is, repeat the column insertion to the left of each set of
lookups.
With the references for the lookup range now being "locked" to columns
D & E, you should be able to select the 2 cells H6 & I6, copy them,
paste them down the rows needed & the same in the new columns across
the page.
Now the new columns can be hidden.


hth
Rob Brockett
NZ
Always learning & 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...

 

[broro183]

Sorry, I didn't word my response very well - I didn't mean for the first
formula to be used at all...
Now that you have a new column H & the "pts" column is column I, try
typing,
In H6 (this column can be hidden later):
=VLOOKUP(G6,$D$6:$E$100,2,FALSE)
and in I6:
=IF(ISNA(H6),0,H6)

Select cells H6 & I6, [ctrl + c], select as many rows down as you need
(in these columns) & press [ctrl + v].
Does this give the results you want in column I?

Re the vlookup working in some cells but not others is strange & may be
due to a quite a number of things - it's hard to know without the
spreadsheet. Reasons include cells being formatted differently (eg as
text), spelling errors (eg extra spaces - do a visual check) or
non-printing characters etc. To overcome any cell formatting problems
try selecting your data (not the headers) & clearing the formats (ie
[alt + e + a + f]).

If this doesn't work, the quickest (since it is only a small
spreadsheet) fix may be to copy the appropriate team in column D (ie
[ctrl + c]) & paste it over the top of the "matching" cell in column G
(ie [ctrl + v]). This way we can be certain that exactly the same thing
is in both cells & the lookup should/will work.

If you keep having problems can you please attach another screendump of
your spreadsheet showing the formulae & we may be able to track the
problem. To do this:
show the formulae press [ctrl + `] (the symbol is at the top left of my
keyboard, by the # 1), select all [ctrl + a], autofit columns [alt + o +
c + a], and post your screen dump.


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[AlienBeans]

Hey...it seemed to have given me what i want in column I, but the file
is too large to upload here... Send me a PM with your email address
and I'll forward it to you. Interestingly enough though, I had to
change the vlookup portion from $D$6 to $D$2 to encompass the entire
column(to include the selections above H6... It worked very well,
except something i find interesting. On cells H9, H11, and H14 it
doesn't show the point total, but on all other H cells that I applied
this formula too, it shows the point totals, just like it shows them in
Column I. You have been very helpfull with me on this, and I really
appreciate it. Send me your email in a PM and i'll forward it to
you..Thanks again.

 

[broro183]

Hi,
I didn't actually answer your question (in pm/email) re the fact tha
it "doesn't show the point total in H9" etc, this is b/c the team bein
looked up didn't actually win & isn't in the column therefore th
formulae return "#N/A".
Column H is just an intermediary column & can be hidden, as the point
for each team are now to be added up in column I (& the similar column
for each team across the sheet).

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..