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
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"?
Originally Posted by sonar
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
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
Is this the same issue with your other post "Formulas not showing results"?
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
>
>
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" <sonar.1up4ug_1125605155.6674@excelforum-nospam.com> wrote in
message news:sonar.1up4ug_1125605155.6674@excelforum-nospam.com...
>
> 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
>
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
>
>
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
Hi bpeltzer
Thanks for the help, will try that.
Sonar
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
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.
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!.
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.
You are missing a ")" for your ISERROR()
Thanks
Found it.
=IF(ISERROR(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE)),"",VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks