Search a string of cells in a row, and return the contents of an adjacent cell

Dennis Wierzbicki

Folks,

I want to be able to search a number of non-contiguous cells in a row to find a certain value, then return the contents of the cell immediately to the left of the cell that contains the value.

I know there has to be a way to do this, but I can't figure it out.

Thanks in advance,

Dennis

Paul Newcome

I can think of a way using an INDEX/MATCH using the MATCH - 1 in the 3rd part of the INDEX function that determines the column number. Can you provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data"?

Paul Newcome

I can think of a way using an INDEX/MATCH using the MATCH - 1 in the 3rd part of the INDEX function that determines the column number. Can you provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data"?

Dennis Wierzbicki

Paul, thanks for getting back with me. The sheet is too large to capture a screen shot, so let me explain.

I have an approval process that moves from left to right within a row in a sheet. The process is comprised of between 5 and 7 Approval Levels that contain a person's name, and in the cell adjacent to the right contains the status of the approval (from an Approval Request). The Status could be Under Review, Approved or Declined.

I have figured out a way to return the Approval Level, returning a number from 0 to 7, by assigning a number to each approval level in a hidden column (AL1, AL2, AL3...), then using the MAX function to return the number, but what I want to do is find the contents of the cell to the left of the number assigned, like "Approved", or "Under Review".

In the case below, if Approval Level 4 was the last Approval Level that contained an entry, I'd like to find "4" (from the MAX cell), and return "Under Review"

Make sense? Thanks in advance.

Paul Newcome

Ok. Is the [AL4] column the only one that is going to have the number 4 in it?

Dennis Wierzbicki

Paul, yes, the others (AL1, AL2, etc.) will all have 1, 2, etc in them.

I believe I figured it out with an INDEX/MATCH combo, based on you!r quick suggestion above.

Thanks

Paul Newcome

Happy to help. 👍️

Can you post your solution in case anyone else is looking for the same thing?

My solution was going to be along the lines of...

=INDEX([First Column]@row:[Last Column]@row, 1, MATCH([Max Column]@row, [First Column]@row:[Last Column]@row, 0) - 1)

Dennis Wierzbicki

Paul, that is essentially what I did:

=IF(ISBLANK([Approval Level Stage]@row), "None", INDEX([Approval Level 1]@row:[AL7]@row, 1, (MATCH([Approval Level Stage]@row, ([Approval Level 1]@row:[AL7]@row)) - 1)))

Thanks again.

Dennis

Dennis Wierzbicki

BTW, I found the [ IF(ISBLANK([Approval Level Stage]@row), "None",] function was required, as what was happening is, if there were no entry returned by the MAX function, the INDEX/MATCH would return the contents of the cell to the left of the first cell in the range (the "zeroth" entry in the range), which wasn't desirable.

Paul Newcome

That's... Odd...

I would think that if there was nothing in the MAX, then the MATCH - 1 would return a (-1) which at most would cause an error for the INDEX function. I would think that having a negative column number would be the same as having a negative row number. That it just wouldn't work. I would have expected an error of some sort.

Dennis Wierzbicki

Paul, no, it returned the contents of the cell immediately to the left of the first cell in the [Approval Level 1]@row:[AL7] range, [Approval Level 1], which had text in it.

Paul Newcome

So you have (essentially)

[Random Column] . | . [Approval Level 1] . | . [AL7]

Your range in the INDEX function does NOT include [Random Column], but it still pulled from that column anyway??

Dennis Wierzbicki

That is correct.

Paul Newcome

I couldn't replicate it. I tried manually entering negative numbers as well as using various functions and combinations of functions and if the result would have pulled from outside of the range established in the INDEX function, I got an error. That was for negative row/column numbers as well as positive row/column numbers (more than what was in the range). Hmm... 🤔