Finding 1st non-null value in Excel

Question

What formula should I use to get the first value in column B that does not equal "NONE" where the value in column A is "a":

enter image description here

Here I want the formula to return "5", because the first row with column A value of "a" and not having column B value of "None" is 5.

No, this isn't the answer I want. You should lookup the whole range. — alwbtc, Apr 29 '14 at 20:20
it already lookups whole range and returns first match as required. — Dmitry Pavliv, Apr 29 '14 at 20:21
This is a duplicate of both [This](http://stackoverflow.com/questions/23372813/match-function-to-match-multiple-values#comment35804407_23372813), [This](http://stackoverflow.com/questions/19307976/find-the-number-of-a-row-that-contains-two-specific-values) , and [this](http://stackoverflow.com/questions/9110429/selecting-a-row-in-excel-based-on-specific-values-in-2-columns) — user2140261, Apr 29 '14 at 20:24
Also, this site is designed to HELP others with there problems, not SOLVE there issues. You should start by trying to solve this on your own, and if you get stuck show us what you tried, and explain why it didn't work. — user2140261, Apr 29 '14 at 20:29

score 1 · Answer 1 · answered Apr 29 '14 at 21:01

1

A simple solution would be to add a modified "key" column:

If you use VLOOKUP, or any other function to pull some info from table, it will return only the first met instance of lookup_value.

I bet you probably want some big stupid formula though.

answered Apr 29 '14 at 21:01

Stepan1010

`I bet you probably want some big stupid formula though.` was a little unneeded. There are situation where a single formula is the best method. 2 being 1) if there are thousands or worse hundreds of thousands of rows, you would add a formula for every single row that would have to recalculate on full worksheet calculations and opens (would be very slow) vs a single formula even if slow would could still be much faster. Plus code maintainability is nice to not have to worry about a random group of 100 of the calculations being shifted to the wrong cells, either by your use of absolute address – user2140261 Apr 29 '14 at 22:01
or an outside macro of outside calculation. Or moving the data around. That said in certain cases your approach is widely accepted as a better approach. So +1. – user2140261 Apr 29 '14 at 22:02
@user2140261 I use long formulas. Sometimes people get a little carried away with their formulas though(myself included). Especially array formulas. It can be a restrictive way of thinking about a problem. Oftentimes, it is much simpler to write a UDF in VBA. Or doing what I did here (and often do) and make helper columns. – Stepan1010 Apr 30 '14 at 14:06

1 Answers1