trying to make if formula work

[nickyd15]

Hi all and thanks in advance for your help and sorry if none of this makes sence

i am trying to make the following formula work with text in all cells

=IF(A2=Sheet4!B2,Sheet4!C2)+IF(A2=Sheet4!B3,Sheet4!C3)+IF(A2=Sheet4!B4,Sheet4!C4)+IF(A2=Sheet4!B5,Sheet4!C5)+IF(A2=Sheet4!B6,Sheet4!C6)

all cells involved are text
the first part of the function works
=IF(A2=Sheet4!B2,Sheet4!C2)
so a2 is nick and sheet4b2 s nick so the formula returns the text from sheet4c2 which is alonso

but as soon as you add the rest of the formula it returns #value!

i know that this would work with numbers but cannot for the life of me figure out how to make it work with text

any helpwould be great

 

[Ron Coderre]

Well....to "fix" your formula, you need to replace the plus signs (+) with ampersands (&) (because you're combining text, not adding numbers) and add an empty string for the non-matches:

=IF(A2=Sheet4!B2,Sheet4!C2,"")&IF(A2=Sheet4!B3,Sheet4!C3,"")
&IF(A2=Sheet4!B4,Sheet4!C4,"")&IF(A2=Sheet4!B5,Sheet4!C5,"")
&IF(A2=Sheet4!B6,Sheet4!C6,"")

But, to use a more maintainable formula, try this:

=IF(COUNTIF(Sheet4!B2:B6,A2),INDEX(Sheet4!C2:C6,MATCH(A2,Sheet4!B2:B6,0)),"")

Does that help?

 

[T. Valko]

Hi all and thanks in advance for your help and sorry if none of this makes sence

i am trying to make the following formula work with text in all cells

=IF(A2=Sheet4!B2,Sheet4!C2)+IF(A2=Sheet4!B3,Sheet4!C3)+IF(A2=Sheet4!B4,Sheet4!C4)+IF(A2=Sheet4!B5,Sheet4!C5)+IF(A2=Sheet4!B6,Sheet4!C6)

all cells involved are text
the first part of the function works
=IF(A2=Sheet4!B2,Sheet4!C2)
so a2 is nick and sheet4b2 s nick so the formula returns the text from sheet4c2 which is alonso

but as soon as you add the rest of the formula it returns #value!

i know that this would work with numbers but cannot for the life of me figure out how to make it work with text

any helpwould be great

Try it like this...

=VLOOKUP(A2,Sheet4!B2:C6,2,0)

 

[nickyd15]

brilliant thank you i all suggestions worked not sure wich i will use yet thanks you again

 

[T. Valko]

brilliant thank you i all suggestions worked not sure wich i will use yet thanks you again

You're welcome. Thanks for the feedback!