Vlookup, IF and N/A#

[luvmyfam]

Hello everyone!!

I need a formula that will use the vlookup formula to find a value on one sheet, but if it returns #N/A or 0 I need it to look on another sheet. Then if it returns #N/A or 0 I need it to remain blank.

Can anyone help me??

Thanks

 

[Jonmo1]

What type of data is the expected result of the vlookup?
Text or Number?
Or could it be either?

 

[luvmyfam]

In one column it will be a number, and then in the next two it will be text

 

[luvmyfam]

Is there a way to combine the two works sheets without duplicating information?

 

[Jonmo1]

This will eliminate the #N/A, but not the 0.
And honestly, it's not worth the extra calculations to hide 0's.
Maybe just go to Tools - Options - View and UNcheck "Zero values"

This is for an expected return of NUMBERS
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(A1,Sheet2!A:B,2,FALSE),VLOOKUP(A1,Sheet3!A:B,2,FALSE)))

If your expected result is TEXT
=LOOKUP(REPT("Z",255),CHOOSE({1,2,3},"",VLOOKUP(A1,Sheet2!A:B,2,FALSE),VLOOKUP(A1,Sheet3!A:B,2,FALSE)))


Actually, the TEXT one does eliminate 0 values as well as #N/A

 

[Gregorys05]

Have you got some sample data at all, if not try looking at the IFERROR excel function this will pick up the #N/A and you could use an if statment to fine the 0's