How to select offset value if condition met in range

[BAM]

Hello! I have a minimal understanding of Excel, and am trying to create a formula that will permit me to select the value from the cell to the left of to another cell that may (or may not) be present in a range of cells within a row.

Attached is a sample file. My goal is to create a formula in cell AP that states, "If the value '101' is found within the range of cells C2 - AO2, put the value from the cell to the left of 101 into cell AP." I am then going to duplicate this formula for different conditions (cells AQ - BV), and then for a variety of ranges.

Thanks so much for your help!

Becca

 

[Luke M]

Hi BAM, and welcome to the forum.

In AQ2, put this formula:
=IFERROR(INDEX($AB2:$AN2,MATCH(AQ$1,$C2:$AO2,0)),"Not found")

The "Not Found" bit can be changed to whatever you want it to do it the sought value isn't in the range.

 

[BAM]

Hi BAM, and welcome to the forum.

In AQ2, put this formula:
=IFERROR(INDEX($AB2:$AN2,MATCH(AQ$1,$C2:$AO2,0)),"Not found")

The "Not Found" bit can be changed to whatever you want it to do it the sought value isn't in the range.

Thanks so much, Luke! I can see I am now much closer, but I am still running into a problem: The formula is producing the result "0" in cell AQ2 when it should be producing "2" (from cell B2 - the cell to the left of the cell with value "101" in cell C2). What am I doing incorrectly? Thank you!

 

[Nebu]

Hi:

The range reference in your formula is wrong, please use the following

=IFERROR(INDEX($D2:$AO2,MATCH(AQ$1,$C2:$AO2,0)),"Not found")

Thanks

 

[BAM]

Absolutely priceless - thank you so much for saving me a huge amount of time!

 

[Aby]

hi not sure if i got it wrong, but the above formula returns value from cell d1...

 

[Luke M]

Bam,
Glad you got it working!
Nebu,
Thanks for the final touch.
Aby,
Not sure which formula you are referring to...

 

[Nebu]

NP Problem Luke , here to help