Alternative to long IFS

[jackcolasu]

Hi!

I'm trying to search data from 1 table and input a corresponding value into another. I'm new to excel so I'm not very formular-savvy, and the solution I came up with is very long. I'm wondering if there is a shorter/faster alternative. I want the data from this table:

To move to this table:

The problem is, the data in table is constantly changing. Today "31" is in cell C5, but tomorrow it could be in cell F7. This is the formula I came up with that works but is long/clunky:

So for room 21, it's searching each cell from C5 to G5 to equal 21 and then returning B5 if true. If there is no true statement, it searches each cell from C6 to G6 to equal 21 and then returns B6 if true, and so on. I don't want to have to type and edit this formula for each room going down the list - is there a simpler way?

 

[Cubist]

Can a patient bed gets assign to multiple people or exactly one person only?

 

[Fluff]

Hi & welcome to MrExcel.
How about in V4 copied down

=TOCOL(IF($C$5:$G$12=U4,$B$5:$B$12,1/0),2)

 

[jackcolasu]

Hi & welcome to MrExcel.
How about in V4 copied down

=TOCOL(IF($C$5:$G$12=U4,$B$5:$B$12,1/0),2)

This worked perfectly, thank you!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Dave Patton]

A no formula solution that you can consider.

Use a Table
With a Table, you can sort:
sort smallest to largest assigned
sort by RN
sort by date
export to another range etc.

T202501a.xlsm
	A	B	C
11	Date	RN_Assigned	Assigned
12	15-Jan	Christina	21
13	15-Jan	Dana	22
14	15-Jan	Lina	23
15	15-Jan	Dana	24
16	15-Jan	Alana	25
17	15-Jan	Shanis	26
18	15-Jan	Alana	27
19	15-Jan	Christina	28
20	15-Jan	Christina	29
21	15-Jan	Christina	30
22	15-Jan	Lina	31
23	15-Jan	Shanis	32
24	15-Jan	Alana	33
25	15-Jan	Lina	34
26	15-Jan	Shanis	35
27	15-Jan	Lina	36.1
28	15-Jan	Dana	36.2
29	15-Jan	Shanis	36.3
30	15-Jan	Dana	36.4
31	15-Jan	Lina	37.1
32	15-Jan	Christina	37.2
33	15-Jan	Shanis	37.2
34	15-Jan	Dana	37.3
35
5b

 

[Fluff]

You can do all that without converting to a table.