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

  • Thread starter Thread starter Nick Guerrier
  • Start date Start date
N

Nick Guerrier

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.
 
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.
Click to expand...

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.
 
Back
Top