Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=543500 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
davesexcel Wrote: 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 -- AlienBeans ------------------------------------------------------------------------ AlienBeans's Profile: http://www.excelforum.com/member.php...o&userid=32352 View this thread: http://www.excelforum.com/showthread...hreadid=543500 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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,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 "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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=543500 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
broro183 Wrote: 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,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 "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... -- AlienBeans ------------------------------------------------------------------------ AlienBeans's Profile: http://www.excelforum.com/member.php...o&userid=32352 View this thread: http://www.excelforum.com/showthread...hreadid=543500 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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 Wrote: 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 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=543500 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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. -- AlienBeans ------------------------------------------------------------------------ AlienBeans's Profile: http://www.excelforum.com/member.php...o&userid=32352 View this thread: http://www.excelforum.com/showthread...hreadid=543500 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Function
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=543500 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup worksheet function | Excel Worksheet Functions | |||
Vlookup with function in table_array | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |