Solved: vlookup skip blank or 0 cells and find match

[obriensj]

Hi, I need to compose an IF(OR (vlookup statement if possible that looks for data in one spreadsheet and if it does not exist in that column move to the next column, pick up that identifer and do the vlookup. So for instance I know that cell A2 (a123) matches cell G2 (a123) so i then bring in the value of 100.
What I need the formula to do is if the next cell is blank or has 0 it knows to move onto the next identifier, so in this case A5 has 0 so look to B5, do the vlookup and and match. Is this possible?
The problem being the formula has to know to when there is a 0 or blank cell it moves to the next identifer, so column A, is blank (or has a zero), then column B, if that is blank( or has a zero), then column C. Is this possible? Please see attached spreadsheet. Many thanks

[Bob Phillips]

This should work

=VLOOKUP(IF(AND(A2<>"",A2<>0),A2,IF(AND(B2<>"",B2<>0),INDEX(G:G,MATCH(B2,H:H,0)),INDEX(G:G,MATCH(C2,I:I,0)))),G:J,4)

[obriensj]

Thanks, it brings in results, however if i delete cell A2 or put in a zero then it does not work, I get #NAME? How can this be fixed?

[Bob Phillips]

When the forum shows that formula, it is inserting a space in the reference H:H. There should be no space, delete it.

[obriensj]

That is perfect, many thanks xld!