SOLVED

# How to extract a column value based on value in the range

Copper Contributor

# How to extract a column value based on value in the range

I have a team ROTA in excel sheet. The first column is the Name and first row is the date. The intervening cells are loaded with values.

 Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Day Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Person 1 S P 2 2 2 2 L 2 2 2 2 2 2 M 2 2 2 2 2 2 2 P S 2 2 2 2 2 2 2 Person 2 2 2 2 2 2 2 L 2 2 L 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 P S Person 3 2 2 2 2 2 2 2 2 2 2 2 2 2 N S P 2 2 2 2 N 2 2 2 2 2 2 2 2 2 Person 4 2 2 2 2 2 2 2 P S 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 Person 5 2 2 2 2 2 2 2 S P 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2

I'm trying to find the dates for a particular person with particular value.

Please guide with a formular through which I can achieve this.

 S P L M N Person 1 1 2 7 14 Person 1 23 22 Person 2 7 Person 2 30 29 10 Person 3 15 16 14 Person 3 21 Person 4 9 8 Person 5 8 9
4 Replies
best response confirmed by venkatvedavyasan (Copper Contributor)
Solution

# Re: How to extract a column value based on value in the range

See the attached workbook.

# Re: How to extract a column value based on value in the range

How did you get the Spill getting filled automatically ? Because when I copy person 1 to 5 in from A10-A14 I'm not getting the value as you got.

Not looking same as what you have got ?

How to achieve this ?

# Re: How to extract a column value based on value in the range

@venkatvedavyasan

In entered the formula in B10, then filled it to the right to F10.

I copied B10:F10, then pasted to B12. B14, B16 and B18.

# Re: How to extract a column value based on value in the range

=IFNA(REDUCE(HSTACK("","S","P","L","M","N"),A3:A7,LAMBDA(a,b,VSTACK(a,REDUCE(b,HSTACK("S","P","L","M","N"),LAMBDA(u,v,HSTACK(u,IFERROR(TOCOL(FILTER(B1:AE1,FILTER(B3:AE7,A3:A7=b)=v)),""))))))),"")

With Office 365 or Excel for the web this formula could be an alternative.

1 best response

Accepted Solutions
best response confirmed by venkatvedavyasan (Copper Contributor)
Solution

# Re: How to extract a column value based on value in the range

See the attached workbook.