CODEWITHSUNDEEP
excelindexingmatch
charles hendry
charles hendry

Reputation: 1740

Return value in Excel when the sum of values in two columns exceed a specified value

I have the following data and want to return the value in column A when the sum of a row from B & C exceeds 400 for the first time.

So B7+C7 = 460, so the return value should be A7, whereas B6+C6 was <400.

This is very easy to do if I add a new column and then lookup the value in that column. However my full dataset has 1000 columns and if possible I would like to do this in a single cell above the datasets.

Sample Data

Upvotes: 0

Views: 1728

Answers (1)

barry houdini
barry houdini

Reputation: 46361

Try this formula

=INDEX(A1:A11,MATCH(TRUE,INDEX(B1:B11+C1:C11>400,0),0))

The MATCH part returns the row number (relative to the range) of the first row that exceeds 400....then INDEX gives you the relevant value from column A

You can easily adapt for larger ranges

Upvotes: 2

Related Questions