Need similar formulas to do the job

sonar · #1 09-01-2005, 03:47 PM

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 · #2 09-01-2005, 04:08 PM

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"?

Quote:

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

sonar · #3 09-01-2005, 04:17 PM

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

Morrigan · #4 09-01-2005, 04:20 PM

Is this the same issue with your other post "Formulas not showing results"?

#5 09-01-2005, 05:05 PM

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

#6 09-01-2005, 05:05 PM

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
>

#7 09-01-2005, 08:05 PM

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

sonar · #8 09-02-2005, 12:15 AM

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 · #9 09-02-2005, 12:15 AM

Hi bpeltzer

Thanks for the help, will try that.

Sonar

sonar · #10 09-02-2005, 11:09 AM

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 · #11 09-02-2005, 11:17 AM

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.

Quote:

Originally Posted by sonar

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 · #12 09-02-2005, 11:20 AM

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

Quote:

Originally Posted by 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

sonar · #13 09-02-2005, 11:48 AM

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 · #14 09-02-2005, 12:02 PM

You are missing a ")" for your ISERROR()

Quote:

Originally Posted by sonar

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 · #15 09-02-2005, 01:11 PM

Thanks

Found it.

=IF(ISERROR(VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE)),"",VLOOKUP(VALUE($A$10),Mo!$A:$D,4,FALSE))

Welcome to the Excel Forum

Forum Statistics