CODEWITHSUNDEEP
exceltext
Andi Domi
Andi Domi

Reputation: 751

Find a text in a given column from differents columns in Excel

I have a excel column with different names, for example :

       a

1     name1
2     name2
3     name2
4     name6
..
1000  name 298

and also have different columns that gives a rande to the $a column , for example

       c        d       e

1    RANGE1   RANGE2  Range3
2    name1    name5   name8
3    name2    name6   name9
4    name3    name7   name10
5    name4            name11

and i would like to make a column $b that checks the column $a for a text and outputs its range name in this case it would be 

       b
1    RANGE1
2    RANGE1
3    RANGE1
4    RANGE2
...
1000 RANGE30

i have tried to make the formula with =if(a1=c2,c1,if(a1=c2,c1,if(..........)) but as you can see it would take forever , and i dont know how to make it less complex and less time consuming . do you have any idea? Thank you in advance and have a great day

Upvotes: 1

Views: 59

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try this one in B1:

=INDEX($1:$1,1,MAX(IF($C:$E=A1,COLUMN($C$1:$E$1))))

and press CTRL+SHIFT+ENTER to evaluate it and then drag formula down.

To make formula faster I suggets you to change $C:$E, to exact range, say $C$1:$E$100 enter image description here

UPD

non-volatile version: use next formula in B1

=INDEX($C$1:$E$1,MAX(IFERROR(MATCH(IF($C$1:$E$100=A1,$C$1:$E$1,""),$C$1:$E$1,0),-1)))

with array entry (CTRL+SHIFT+ENTER) and drag it down.

Upvotes: 1

Related Questions