Syntax for using "IF"...

G

garry05

Trying to resolve a situation that I've got posted elsewhere on here;
maybe I can do it this way if someone can help me with the syntax for
using "IF".

Say that column G is numbered 1-5, and column H has values of 100, 133,
322, 440, 560.

I'll enter a number (1-5) in col A, and I want this to happen in col B,
and I'll express it in human language: If A2=3 then B2=322...If A1=5
then B1=560.

In other words, I post a number in A, then go search G for the same
number, then post the corresponding H value into B.

I've tried the syntax every way but the right way, I guess.


Thanks,
Garry
 
J

Jerry Price

Sounds like you should be using "SUMIF" rather than IF.

=SUMIF(Range, criteria, Sum range)

For your example, =sumif(G$1:G$5, A$1:A$5, H$1:H$5)


Jerry
 
R

Roger Govier

Hi Garry

Did you try the solution I posted earlier. In case you didn't see it I
repeat it here:-

Hi Garry
There are various ways to solve this problem.
If there are only to be 5 values then in B1
=CHOOSE(A1,100,133,322,440,560)
is probably the easiest.

If you wanted to know the syntax of IF statements, then
=IF(A1="","",IF(A1=5,560,IF(A1=4,440,IF(A1=3,322,IF(A1=2,133,100)))))

If there are to be a lot more values, then it would be better to use a
VLOOKUP() function.

Regards

Roger Govier
 
H

hhalle

Garry,

The VLOOKUP() function is the simplest way:

The formula in B1 would look as follows:

=VLOOKUP(A1,G1:H5,2)

It will serach for the corresponding value you entered in A1 in the
column G1:G5 and show the value in the second column H1:H5

Good Luck

Hans
 
R

Ragdyer

What you're describing here is a simple datalist (G1 to H5), which you want
polled, using the values entered in Column A.

As described, this can easily be accomplished by using the Lookup()
function.

In B1, enter this formula:

=LOOKUP(A1,$G$1:$H$5)

And copy down as needed.

Now, this can become a little more complex if the values in Column A do not
*exactly* match the values in Column G,
OR
The values in Column G are not *always* as you originally described here
(sorted in ascending order).

So, if this is an over simplified example of your actual anticipated data,
the suggested formula *might not* be a viable solution.

You might care to post back with *exact* examples of your data values for
both Column G and Column A.
 
Top