Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need similar formulas to do the job
Hi I am currently using the following formula =SUMIF(Mo!$A$1:$A$999,$A10,Mo!$D$1:$D$999) the problem is, I dont want to use the SUMIF, I want to use the VLOOKUP, but the VLOOKUP does not want to work so nicely. My other formulas are easily "rattled" should I try and touch the formatting of the sheets cells. Is there any other functions I can use that will give me what I want? Regards Sonar :) -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#2
|
|||
|
|||
SUMIF() and VLOOKUP() are completely different. SUMIF() adds up all the numbers that meet the criteria and VLOOKUP() searches a target value and returns its corresponding value. I understand all your data must be unique. Thus VLOOKUP() can actually perform the job. Now in order for VLOOKUP() to work properly, your data must be sorted. What do you mean by "VLOOKUP does not want to work so nicely"? sonar Wrote: Hi I am currently using the following formula =SUMIF(Mo!$A$1:$A$999,$A10,Mo!$D$1:$D$999) the problem is, I dont want to use the SUMIF, I want to use the VLOOKUP, but the VLOOKUP does not want to work so nicely. My other formulas are easily "rattled" should I try and touch the formatting of the sheets cells. Is there any other functions I can use that will give me what I want? Regards Sonar :) -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#3
|
|||
|
|||
VLOOKUP is not a subsitute for using SUMIF. Myabe what you mean is you want
to lookup A10 in that range. If so, try =VLOOKUP($a10,Mo!$A$1:$D$999,4,false) "sonar" wrote: Hi I am currently using the following formula =SUMIF(Mo!$A$1:$A$999,$A10,Mo!$D$1:$D$999) the problem is, I dont want to use the SUMIF, I want to use the VLOOKUP, but the VLOOKUP does not want to work so nicely. My other formulas are easily "rattled" should I try and touch the formatting of the sheets cells. Is there any other functions I can use that will give me what I want? Regards Sonar :) -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#4
|
|||
|
|||
my 13 digit numbers only works in cells formated as Text, unfortunatly, if I try to have them any other way, the other formula that puts in the description, does not work. Vlookup does not want to match up text formulated info with General formulated info. I know that Sumif calc. information, but it works where vlookup does not. The problem is, I dont want it to calculate. Any idea what I can use here? Regards -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#5
|
|||
|
|||
Is this the same issue with your other post "Formulas not showing results"? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#6
|
|||
|
|||
What makes you think that you want VLOOKUP, it is nothing like SUMIF.
-- HTH RP (remove nothere from the email address if mailing direct) "sonar" wrote in message ... Hi I am currently using the following formula =SUMIF(Mo!$A$1:$A$999,$A10,Mo!$D$1:$D$999) the problem is, I dont want to use the SUMIF, I want to use the VLOOKUP, but the VLOOKUP does not want to work so nicely. My other formulas are easily "rattled" should I try and touch the formatting of the sheets cells. Is there any other functions I can use that will give me what I want? Regards Sonar :) -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#7
|
|||
|
|||
You can use 'trim' or 'value' to force the key value to the needed type to
work inside the vlookup. Ex: =vlookup(value(a10),Mo!$a:$d,4,false) if the values in column A on the Mo worksheet are numeric but cell a10 contains text. =vlookup(trim(a10),Mo!$a:$d,4,false) should work in the opposite case. As others noted, this all requires that your values in column A on the Mo worksheet are unique. Otherwise vlookup would NOT be a suitable replacement for sumif. "sonar" wrote: my 13 digit numbers only works in cells formated as Text, unfortunatly, if I try to have them any other way, the other formula that puts in the description, does not work. Vlookup does not want to match up text formulated info with General formulated info. I know that Sumif calc. information, but it works where vlookup does not. The problem is, I dont want it to calculate. Any idea what I can use here? Regards -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#8
|
|||
|
|||
Hi Morrigan I guess you can say that. I tried the Text to Column story, It worked, but it messed up all my formulas I used for the 13 digit barcodes. So I decided to move the information from my stock to the main sheet, and try something alternative. Regards -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#9
|
|||
|
|||
Hi bpeltzer Thanks for the help, will try that. Sonar -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#10
|
|||
|
|||
Hi, cant figure it out What is wrong with this formula? =IF(ISERROR(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) ,"",(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE)) Regards -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#11
|
|||
|
|||
It will help us help you if you can be more specific. "I tried the Text to Column story. It worked, but it messed up all my formulas I used for the 13 digit barcodes." is not very informative. If after the changes it worked on one sheet but messed up others, maybe you can try convert all your barcodes on all sheets using the same approach. If it does not solve the issue, "it messed up all my formulas" does not tell us enough. sonar Wrote: Hi Morrigan I guess you can say that. I tried the Text to Column story, It worked, but it messed up all my formulas I used for the 13 digit barcodes. So I decided to move the information from my stock to the main sheet, and try something alternative. Regards -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#12
|
|||
|
|||
Formula looks fine to me, but why do you need to use VALUE()? Depending on what's in A10, you may or may not get a result. For instance if A10 contains text, you will get #VALUE!. sonar Wrote: Hi, cant figure it out What is wrong with this formula? =IF(ISERROR(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) ,"",(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE)) Regards -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#13
|
|||
|
|||
Hi I used the formula =VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) because it seems to work and it does the job, I am not even going to ask why it works, or try to explain how it works, its beyond me, I know when I am defeated. but I have formulated it to =IF(ISERROR(IF($AH10=(VLOOKUP(VALUE($A10),We!$A:$D ,4,FALSE)),"",(VLOOKUP(VALUE($A10),We!$A:$D,4,FALS E)))),"",(IF($AH10=(VLOOKUP(VALUE($A10),We!$A:$D,4 ,FALSE)),"",(VLOOKUP(VALUE($A10),We!$A:$D,4,FALSE) )))) and that works but now I want to formulate =VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) in column AH10 not to give me an #N/A error, by doing the following: =IF(ISERROR(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) ,"",VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE)) but everytime I do it, it complains about the ,"",. Any idea what stupid mistake I could have made here? I mean, there must be something wrong with the formula for excel to complain about it. -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#14
|
|||
|
|||
You are missing a ")" for your ISERROR() sonar Wrote: Hi I used the formula =VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) because it seems to work and it does the job, I am not even going to ask why it works, or try to explain how it works, its beyond me, I know when I am defeated. but I have formulated it to =IF(ISERROR(IF($AH10=(VLOOKUP(VALUE($A10),We!$A:$D ,4,FALSE)),"",(VLOOKUP(VALUE($A10),We!$A:$D,4,FALS E)))),"",(IF($AH10=(VLOOKUP(VALUE($A10),We!$A:$D,4 ,FALSE)),"",(VLOOKUP(VALUE($A10),We!$A:$D,4,FALSE) )))) and that works but now I want to formulate =VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) in column AH10 not to give me an #N/A error, by doing the following: =IF(ISERROR(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) ,"",VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE)) but everytime I do it, it complains about the ,"",. Any idea what stupid mistake I could have made here? I mean, there must be something wrong with the formula for excel to complain about it. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
#15
|
|||
|
|||
Thanks Found it. =IF(ISERROR(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE) ),"",VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE)) -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401265 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste formulas between workbooks without workbook link | Excel Discussion (Misc queries) | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
locking formulas?? | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |