CODEWITHSUNDEEP
excel
MrCarder
MrCarder

Reputation: 438

Find a specified string in row, then return the value of the cell before that string.

I have a spreadsheet that displays a list of users and their management chain. It looks something like this: 

User | Manager 1 | Manager 2 | Manger 3 | ... |

*where Manager 1 is who they report to directly, Manager 2 is who Manager 1 reports to, etc.

I need to return a value for each user's SVP - who all report to the CEO (ex. "John Doe"). So if "John Doe" appears in the Manager 7 column for User A, I want the value of the Manager 6 column.

Also, whoever created this spreadsheet listed "John Doe" in all remaining Manager columns if the name appeared before the final column. If Manager 2 equals "John Doe", Manager 3-Manager 10 will also equal "John Doe". So I need to identify the first appearance of "John Doe" as well.

Can anyone point me in the right direction?

Thank you in advance!

Upvotes: 0

Views: 193

Answers (1)

MrCarder
MrCarder

Reputation: 438

UPDATE - SOLVED

I was playing around with the INDEX() function and found a solution. Here's what worked for me: 

INDEX(B2:M2, MATCH("Doe, John",B2:M2,0)-1)

Ended up being pretty simple

Upvotes: 1

Related Questions