Find text in row and return the next cell contents

[dmarchand1]

Hi - Is there a way to look across a row of data, find a specific text value, then return the text in the cell to its right? In the example below, I want to search each row across columns BD:BO, if I find the text "Vice President" in any cell within that row, I want to bring back the text in the next cell to the right, in this case the name of the Vice President. I haven't found a way to use Hlookup to do this. Any thoughts?

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LOOKUP(2,1/(BD2:BN2="Vice President"),BE2:BO2)

 

[dmarchand1]

Hi & welcome to MrExcel.
How about

=LOOKUP(2,1/(BD2:BN2="Vice President"),BE2:BO2)

Thank you very much for the suggestion. I am getting #N/A. what is the number 2 represent in the parenthesis?

 

[Fluff]

You can hide the #N/A for rows with no VP like

=IFNA(LOOKUP(2,1/(BD2:BN2="Vice President"),BE2:BO2),"")

 

[dmarchand1]

You can hide the #N/A for rows with no VP like

=IFNA(LOOKUP(2,1/(BD2:BN2="Vice President"),BE2:BO2),"")

I was using the formula incorrectly. I needed to extend the range, then it worked perfectly. Thank you very much! If you have time, would you explain how the formula is functioning? for example, why is there 1/1/(BD2:BN2="Vice President")_?

 

[Fluff]

The (BD2:BN2="Vice President")returns an array of true or false & then when 1 is divided by that array you get an array of 1s and #Div0s, it then looks for 2 in that array as an exact match or the next smallest number

 

[dmarchand1]

The (BD2:BN2="Vice President")returns an array of true or false & then when 1 is divided by that array you get an array of 1s and #Div0s, it then looks for 2 in that array as an exact match or the next smallest number

Thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.