Vlookup to return First Non-Zero Value in Array

Thanks:  0
Likes:  0

# Thread: Vlookup to return First Non-Zero Value in Array

1. ## Vlookup to return First Non-Zero Value in Array

I want to create a vlookup formula but instead of returning the first number in array, I want it to return the first number that is non-zero in the array

EG: I want vlook to return \$1 for product ABC and \$3 for product DEF.

Product | Jan | Feb | March |
-----------------------------------------
ABC | \$0 | \$1 | \$2 |
DEF | \$3 | \$4 | \$5 |

Is this possible perhaps with an array formula?

2. ## Re: Vlookup to return First Non-Zero Value in Array

One possibility:

Sheet3

 A B C D 1 Product Jan Feb March 2 ABC 0 1 2 3 DEF 3 4 5 4 5 Product 6 ABC 1

 Cell Formula B6 {=INDEX(\$B\$2:\$D\$3,MATCH(\$A6,\$A\$2:\$A\$3,0),MIN(IF(\$A\$2:\$A\$3=\$A6,IF(\$B\$2:\$D\$3<>0,COLUMN(\$B\$2:\$D\$3)-COLUMN(\$B\$2)+1))))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Vlookup to return First Non-Zero Value in Array

Here's a similar way...

B6, confirmed with CONTROL+SHIFT+ENTER:

=INDEX(\$B\$2:\$D\$3,MATCH(\$A6,\$A\$2:\$A\$3,0),MATCH(TRUE,INDEX(\$B\$2:\$D\$3,MATCH(\$A6,\$A\$2:\$A\$3,0),0)>0,0))

Actually, if you want to return the results on the same row, maybe...

A1:E3...

 Product Jan Feb March ABC 0 1 2 1 DEF 3 4 5 3

E2, confirmed with CONTROL+SHIFT+ENTER, and copy down:

=INDEX(B2:D2,MATCH(TRUE,B2:D2>0,0))

4. ## Re: Vlookup to return First Non-Zero Value in Array

what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".

5. ## Re: Vlookup to return First Non-Zero Value in Array

Originally Posted by fju2112
what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".
Can you post some sample data and let us know what result you expect?

6. ## Re: Vlookup to return First Non-Zero Value in Array

Originally Posted by fju2112
what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".
Perhaps...

=INDEX(B2:D2,MATCH(TRUE,B2:D2<>"",0))

You need to apply control+shift+enter to the formula, that is, press down the control and shift keys at the same time while you hit the enter key. When done properly, a pair of curly braces, { and }, appear around the formula.

If the foregoing is not what you want, try to elaborate by means of a small sample.

7. ## Re: Vlookup to return First Non-Zero Value in Array

Originally Posted by fju2112
what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".
Originally Posted by T. Valko
Can you post some sample data and let us know what result you expect?
Is this what you had in mind?

Sheet1

 A B C D E F 1 _ this text _ _ this 2 that none _ some _ that 3 _ _ _ other _ other 4 _ _ stuff more _ stuff

This formula entered in F1 and copied down:

=INDEX(A1:D1,MATCH("*",A1:D1,0))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•