CODEWITHSUNDEEP
excelvlookup
George H
George H

Reputation: 27

Excel Vlookup to loop through values until Value is not 0

I have this data,

Roots data

ID Amount
1  *Blank*
1  10
2  20
3  30

transactions data

ID    BID 
1      ?
2      ?
3      ?

I vlookup the ID into a different sheet to find the Amount for a particular ID. Butt its current only displaying the Amount at the first instead of the ID.

I am using: =vlookup(A2,'Roots data'!F2:G1855,2,FALSE) at the moment.

Upvotes: 0

Views: 655

Answers (1)

tigeravatar
tigeravatar

Reputation: 26650

Give this a try:

=INDEX('Roots data'!$G$2:$G$1855,MATCH(1,INDEX(('Roots data'!$F$2:$F$1855=A2)*('Roots data'!$G$2:$G$1855<>"")*('Roots data'!$G$2:$G$1855<>0),),0))

EDIT: Formula will now ignore results that are 0 as well as blanks.

Upvotes: 1

Related Questions