IF function help

B

bulsy

I have a table with names going down the column, and then numbers in the 7
columns to the side. Below that, I created another column of all the
possible numbers. I then created a conditional to look through each number
column above and see if it will match the corresonding number of the cell I
am in below. If it matches, I need to it to display the name that
corresponds to the number match. However, it seems that it wont display the
name for some reason. I know the formula is right because if I were to
manipulate it so it would display a number instead, this works. It seems
that the problem is in displaying the name as it's not a number. Anyone know
an additional function I need to insert to get the name displayed?
 
B

bulsy

Below is an example of what I have built. I want it to look at the list in
Col B and match it to the corresponding number below, then to display "Steve"
next to 1, "Joe" next to 2, and so on... I want this all to be automatic so
that in subsequent columns when the number changes, the name changes below as
well.

Col A Col B Col C
Adam 4 2
Steve 1 3
Frank 3 1
Joe 2 4

1: Steve Frank
2: Joe Adam
3: Frank Steve
4: Adam Joe
 
G

Gaurav

=IF($A2=B$7,$A$7,IF($A2=B$8,$A$8,IF($A2=B$9,$A$9,IF($A2=B$10,$A$10,""))))

Drag down and right.

IF(B1=1,"Steve",IF(B1=2,"Joe",IF(B1=3,"Frank",IF(B1=4
 
G

Gaurav

enter this formula in B2 and then drag it as I assumed that your names are
from A1 to A4 and the table below starts from A7.
 
B

bulsy

Im not sure if Im explaining this correctly. In the blank lines below, this
is the formula that I have entered: {=SUM(IF(B1:B4=A6, A1:A4," "))}
As I said before, if the names were numbers (say, 100 - 104) it would return
the number value, but since it's a name it returns a "0". I want to be able
to automatically fill in the blank spaces using the columns.
 
G

Gaurav

why dont you do this.

=IF($A7=B$1,$A$1,IF($A8=B$2,$A$2,IF($A9=B$3,$A$3,IF($A10=B$4,$A$4,""))))

earlier formula was returning numbers in the upper table looking at the
lower table. this should return names in the lower table looking at the
upper table.
 
G

Gaurav

you cant SUM names.


bulsy said:
Im not sure if Im explaining this correctly. In the blank lines below,
this
is the formula that I have entered: {=SUM(IF(B1:B4=A6, A1:A4," "))}
As I said before, if the names were numbers (say, 100 - 104) it would
return
the number value, but since it's a name it returns a "0". I want to be
able
to automatically fill in the blank spaces using the columns.
Click to expand...
 
Top