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

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

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

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

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

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

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


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=401265

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

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

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

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

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

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

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