VLookup across multiple columns, returning data if it exists

Hello,

I am trying to write a VLookup that will look across multiple columns, returning the data if it exists.

Example:

A______B________C_______D
Acct__Widget 1__Widget 2__Widget 3
X_______5
Y________________________4
Z________________7

I want the VLookup to reference the account number (X,Y or Z) and return the value from either column B, C or D. If there is no data in any of the columns I would like it to return "N/A" or 0. The columns are mutually exclusive, so if there is data that exists it will only exist in one of the columns.

I've tried using IF(ISNA(Vlookup...functions without much luck. Any help would be appreciated.

Thanks.

A B C D E
Accnt W1 W2 W3 Answer
X 2 2
Y 6 6
Z 5 5

Type this formula in E2

IF(VLOOKUP(A3,$A$3:$B$5,2,0)<>"",VLOOKUP(A3,$A$3:$B$5,2,0),IF(VLOOKUP(A3,$A$3:$C$5,3,0)<>"",VLOOKUP(A3,$A$3:$C$5,3,0),IF(VLOOKUP(A3,$A$3:$D$5,4,0)<>"",VLOOKUP(A3,$A$3:$D$5,4,0),"N/A")))

Hope this helped you out, then click Add Reputation

Originally Posted by ChemistB

=INDEX($B$2:$D$4, MATCH(A7,$A$2:$A$4,0),MATCH(1E+300,INDEX($B$2:$D$4,MATCH(A7,$A$2:$A$4,0),)))

Dear ChemistB, our Guru, please, could you explain what 1E+300 means? Thank you for that. Petr