Lookup Problem

[Gbonda]

Hello,

I use this formula to sum numbers. I am trying to use the same formul
to return text. What do I have to replace sum with?

{=SUM(IF(A2>=Sheet2!$E$1:$E$51,IF(Sheet1!A2<=Sheet2!$F$1:$F$51,Sheet2!$G$1:$G$51,0)))}

Thanks
Gre

 

[Frank Kabel]

Hi
thre's no such build-in function. How do you want to 'Sum' text
values?.

 

[Gbonda]

I am sorry I was not clear.

What I meant was say column G is filled with text values. I tried t
used T and Text insted of sum but they did not work.

{=SUM(IF(A2>=Sheet2!$E$1:$E$51,IF(Sheet1!A2<=Sheet2!$F$1:$F$51,Sheet2!$G$1:$G$51,0)))

 

[Frank Kabel]

Hi
still not clear how you want to comine the returned text values?

e.g. you would get the values
text1
text2

What would your expected 'summed' result be?
text1text2
or
text1, text2

In both cases you'll need VBA for this. There's no build-in function to
concatenate an array of strings. e.g.
=CONCATENATE(A1:A10)
does not work

 

[Aladin Akyurek]

Control+shift+enter:

=INDEX(Sheet2!$G$1:$G$51,MATCH(1,(A2>=Sheet2!$E$1:$E$51)*(Sheet1!A2<=Sheet2!
$F$1:$F$51),0))