If cell equals a text value return a value from corresponding cell
 

A B C
1 Room Service Use
2 10100 PSYCHIATRY MUSIC RM

Basically I need a formula that wil take If B2 = "psychiatry", print(or
list) corresponding A cell, in this case 10100. I'd liek to be able to
print(list) all the rooms of the hospital under their corresponding service.
I can total, across floors, but I am having a bear getting what I what as
described.


--
Thanks, Nick

If cell equals a text value return a value from correspondingcell
 

On 5 June, 21:13, Nick Guerrier wrote:
A * * * * * * * B * * * * * * * * * * * * * * C *
1 Room * * * * Service * * * * * * * * * *Use
2 10100 PSYCHIATRY * * *MUSIC RM

Basically I need a formula that wil take If B2 = "psychiatry", print(or
list) corresponding A cell, in this case 10100. I'd liek to be able to
print(list) all the rooms of the hospital under their corresponding service.
I can total, across floors, but I am having a bear getting what I what as
described.

--
Thanks, Nick

Use a combination of

MATCH (to work out which row in column B it is found)

and

INDEX (to return the value in column 1 row x (from above)

=INDEX(A:B,MATCH("PSYCHIATRY",B:B),1)

returns

10100

as required.

If cell equals a text value return a value from corresponding cell
 

have you tried using a pivot table?

"Nick Guerrier" wrote in message
...
A B C
1 Room Service Use
2 10100 PSYCHIATRY MUSIC RM

Basically I need a formula that wil take If B2 = "psychiatry", print(or
list) corresponding A cell, in this case 10100. I'd liek to be able to
print(list) all the rooms of the hospital under their corresponding
service.
I can total, across floors, but I am having a bear getting what I what as
described.


--
Thanks, Nick