vlookup & if found #N/A get another column value

[hsandeep]

In workbook2 I am doing vlookup to get values from workbook1 (Sheet1). Criteria is: If the value in the Col_index_num is null or #N/A then I want value from another column.
Ex:
Workbook1
A1=PPP-100 B1=PETER C1=#N/A
Workbook2
A1=PPP-100
D1 (output where formula is required)=vlookup(A1,Table_array,3,0) is giving #N/A (C1) but then I require B1 i.e. Answer=PETER
How to accomplish?

 

[Canapone]

Hi,

first attempt.

I'm using Excel 2000

=IF(ISERROR(vlookup(A1,Table_array,3,0),vlookup(A1,Table_array,2,0),vlookup(A1,Table_array,3,0))

If you're are using Excel 2007 or more

=IFERROR(vlookup(A1,Table_array,3,0),vlookup(A1,Table_array,2,0))

Hope it helps you to find the right solution.

Cheers

 

[patel45]

Hi Canapone, here you too ?

 

[bosco_yip]

Or D1 :

=VLOOKUP(A1,Table_array,ISTEXT(C1)+2,0)

Regards

 

[hsandeep]

=IFERROR(vlookup(A1,Table_array,3,0),vlookup(A1,Table_array,2,0))

If C1=null then?

 

[hsandeep]

[QUOTE

=IFERROR(vlookup(A1,Table_array,3,0),vlookup(A1,Table_array,2,0))
This did not work. Should it be other way round?

 

[Canapone]

Hi all,


=IF(OR(ISERROR(vlookup(A1,Table_array,3,0)),vlookup(A1,Table_array,3,0)="null")),vlookup(A1,Table_array,2,0),vlookup(A1,Table_array,3,0))


Hope parentheses are correct and the formula has some logic.

Cheers


------------------
Ciao Patel45

 

[hsandeep]

Canapone,

C1 can be either "" or #N/A

 

[Canapone]

Hi again,

maybe

=IF(OR(ISERROR(vlookup(A1,Table_array,3,0)),ISBLANK(vlookup(A1,Table_array,3,0))),vlookup(A1,Table_array,2,0),vlookup(A1,Table_array,3,0))

I'm sure you'll get more elegant solutions from the Forum

Cheers

 

[jai9]

Hi,

Try

=IF(OR(vlookup(A1,Table_array,3,0)=#NA,vlookup(A1,Table_array,3,0)=""),vlookup(A1,Table_array,2,0),vlookup(A1,Table_array,3,0))

Jai