Need similar formulas to do the job

  • Thread starter Thread starter sonar
  • Start date Start date
S

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 :)
 
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"?
 
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)
 
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
 
What makes you think that you want VLOOKUP, it is nothing like SUMIF.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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.
 
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
decided to move the information from my stock to the main sheet, and tr
something alternative.

Regard
 
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,FALSE)))),"",(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()

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,FALSE)))),"",(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.
Click to expand...
 
Thanks

Found it.

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