refer multiple external csv files returns #VALUE! error in vlookup function

**dryice1993** · 11-28-2012, 05:46 AM

I have a master excel sheet,abc.xls and I need it to refer to columns found in 2 other csx files:def.csv and ghj.csv.I tried using the VLOOKUP function:

=VLOOKUP(F2,[def.csv]def!R2C15:R439C15+[ghi.csv]ghi!R2C9:R1248C9,1,FALSE)

where F2 is a column in abc.xls and R2C15:R439C15 are the rows referred in def.csv and R2C9:R1248C9 are the rows referred in ghi.csv.However,when I tried this formula in the master copy(abc.xls),it returns a #VALUE! error.As I am not too sure on how to refer to multiple external csv files,could anybody guide me on how to modify this formula?

**:) Sixthsense :)** · 11-28-2012, 05:52 AM

You have to use two separate vlookups in this case.

If the Result is Text Data

Formula:

=VLOOKUP(F2,[def.csv]def!R2C15:R439C15,1,FALSE)&VLOOKUP(F2,[ghi.csv]ghi!R2C9:R1248C9,1,FALSE)

If the Result is Numeric

Formula:

=VLOOKUP(F2,[def.csv]def!R2C15:R439C15,1,FALSE)+VLOOKUP(F2,[ghi.csv]ghi!R2C9:R1248C9,1,FALSE)

**dryice1993** · 11-28-2012, 06:14 AM

I tried the formula given(data is text),but it still returns #VALUE! in the cells.

**:) Sixthsense :)** · 11-28-2012, 06:21 AM

Formula:

=VLOOKUP(F2,[def.csv]def!R2C15:R439C15,1,FALSE)&VLOOKUP(F2,[ghi.csv]ghi!R2C9:R1248C9,1,FALSE)

The above formula won't get #VALUE! but it may get #N/A when there is no match is found in any of the formula.

The below one won't get #N/A also

Formula:

=IF(isna(VLOOKUP(F2,[def.csv]def!R2C15:R439C15,1,FALSE)),"",VLOOKUP(F2,[def.csv]def!R2C15:R439C15,1,FALSE))&if(isna(VLOOKUP(F2,[ghi.csv]ghi!R2C9:R1248C9,1,FALSE)),"",VLOOKUP(F2,[ghi.csv]ghi!R2C9:R1248C9,1,FALSE))