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

Copper Contributor

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.


Person 1SP2222L222222M2222222PS2222222
Person 2222222L22L222222222222222222PS
Person 32222222222222NSP2222N222222222
Person 42222222PS222222222222222222222
Person 52222222SP222222222222222222222


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.


Person 112714 
Person 12322   
Person 2  7  
Person 2302910  
Person 31516  14
Person 3    21
Person 498   
Person 589   
3 Replies
best response confirmed by venkatvedavyasan (Copper Contributor)


See the attached workbook.

@Hans Vogelaar 


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 ? 


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.