VLookup Function

[AlienBeans]

WENFootballPoll.JPG

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 [email protected] if you can help...thanks...

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

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]

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 that it "doesn't show the point total in H9" etc, this is b/c the team being looked up didn't actually win & isn't in the column therefore the formulae return "#N/A".
Column H is just an intermediary column & can be hidden, as the points for each team are now to be added up in column I (& the similar columns for each team across the sheet).

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