PDA

View Full Version : Return the value below the cell that contains a specific string



Johnatha
02-08-2016, 01:53 PM
Hi everyone,

I hope my title explains it. I want to search a specific range (i.e. B5:S5) for a specific text string (i.e. "Already Eligible"). Since that string only appears once in the range, I want to return the cell's value directly below it (a number).

So far I have this formula "=INDEX(B5:S5,MATCH("Already Eligible",B5:S5,0)-1)" which returns the value of the cell to the left of the cell containing "Already Eligible". Is it possible to grab the cell bellow's value instead?
Here's what my data looks like:



2015
2016
2017
2018
2019
2020
Already Eligible
2021
2020
Total


8
65
55
45
52
8
2
6
65
851



Thanks!

mancubus
02-08-2016, 03:16 PM
hi.

?

=OFFSET(INDEX(B5:S5,MATCH("Already Eligible",B5:S5;0)),1,0)

offset 1 row down and 0 column.

p45cal
02-08-2016, 03:21 PM
=INDEX(B6:S6,MATCH("Already Eligible",B5:S5,0))

Johnatha
02-10-2016, 11:57 AM
Thank you both mancubus and p45cal. Those formulas both worked! :)