How to write a formula with IFERROR and vlookup, when the data to be looked up are in a separate sheet within the same file? Thanks for teaching me.

✔ Best Answer

Looking over your formula I'm not sure you have the syntax correct. =IFERROR(IF(ISNA(VLOOKUP(Hitachi!Summary[@PartNo],Hitachi!SparePartList[@SparePartParticulars],2,0)),"",VLOOKUP(Hitachi!Summary[@PartNo],Hitachi!SparePartList[@SpartPartParticulars],2,0)),"No description found")

The syntax of a Vlookup is:

=VLOOKUP( value, table, index_number, [not_exact_match] )So your lookup

valueis: Hitachi!Summary[@PartNo]The @PartNo needs to be a specific cell

and if your in theHitachiworkbook you

do not need to include the name in the formula.Your

tableis: @SparePartParticularsIt should be two or more columns

and is the range of cells that contains the data your looking for.

Somthing like: B1:C20Your

index_numberis: 2The index number is the column number,

in table, from which the matching value is returned

so you are looking in the second column of your table array.The last is you want an Exact Match, 0

So your formula should look somthing like:

=IF(ISNA(VLOOKUP(Summary!A1,SparePartList!B1:C20,2,0)),"No description found",VLOOKUP(Summary!A1,SparePartList!B1:C20,2,0))

If your first Lookup fails, it will return the "No Description found" message,

else it returns the value from the second column in your table.Is that what your looking for?

MIKE

message edited by mmcconaghy

Post the formula you have tried,

and we'll see if we can't put you on the right track.Give as much info as possible, like what the cells contain, number or text,

also how everything is set up, will help,If you can, post a small sample of your sheet, with a before & after,

but first read this How-To which explains the use of the < PRE > tags

to align your data.http://www.computing.net/howtos/sho...

MIKE

message edited by mmcconaghy

The following is the formula I wrote. The error message seemed to be Invalid Names, Invalid formula. =IFERROR(IF(ISNA(VLOOKUP(Hitachi!Summary[@PartNo],Hitachi!SparePartList[@SparePartParticulars],2,0)),"",VLOOKUP(Hitachi!Summary[@PartNo],Hitachi!SparePartList[@SpartPartParticulars],2,0)),"No description found")

Here are more infoormation:

1. All data are put in 1 file (File name: Hitachi) that contains 10 separate spreadsheets

(Sheet name: Summary, Lift 1, Lift 2.........Lift 8, SparePartList)

2. In sheets 'Summary', 'Lift 1', 'Lift 2' etc., possibly the same Spare Part No. (Column name: SparePartNo) maybe input under the column 'SparePartNo'

3. What I want to do is to use IF or IFERROR, and VLOOPKUP to test the SparePartNo in sheets 'Summary', 'Lift 1', 'Lift 2' etc., and call up the corresponding Spare Part Particulars from the column 'SparePartParticulars in the sheet 'SparePartList', and let them appear automaticaaly in the sheets 'Summary', 'Lift 1', 'Lift 2' etc.

Thanks again for giving guidance.

Bryan

I'm trying to set up a workbook similar to what you have, so a couple of questions,

The error message seemed to be Invalid Names, Invalid formulaWhat was the error message?

Are you using Named Ranges, or Table Headings in your formula:

Also, what is the purpose of the @ symbol in your formula?

I've seen hash marks, #, used before in Tables.Also what type of data are you searching for, Text or Numbers,

I'm guessing probably Text as most part numbers are a mix

of letters and numbers.If you can post a small sample of your data.

MIKE

Looking over your formula I'm not sure you have the syntax correct. =IFERROR(IF(ISNA(VLOOKUP(Hitachi!Summary[@PartNo],Hitachi!SparePartList[@SparePartParticulars],2,0)),"",VLOOKUP(Hitachi!Summary[@PartNo],Hitachi!SparePartList[@SpartPartParticulars],2,0)),"No description found")

The syntax of a Vlookup is:

=VLOOKUP( value, table, index_number, [not_exact_match] )So your lookup

valueis: Hitachi!Summary[@PartNo]The @PartNo needs to be a specific cell

and if your in theHitachiworkbook you

do not need to include the name in the formula.Your

tableis: @SparePartParticularsIt should be two or more columns

and is the range of cells that contains the data your looking for.

Somthing like: B1:C20Your

index_numberis: 2The index number is the column number,

in table, from which the matching value is returned

so you are looking in the second column of your table array.The last is you want an Exact Match, 0

So your formula should look somthing like:

=IF(ISNA(VLOOKUP(Summary!A1,SparePartList!B1:C20,2,0)),"No description found",VLOOKUP(Summary!A1,SparePartList!B1:C20,2,0))

If your first Lookup fails, it will return the "No Description found" message,

else it returns the value from the second column in your table.Is that what your looking for?

MIKE

message edited by mmcconaghy

Thanks a lot! I'll try it out and see if it works :)

Bryan

message edited by bryany361

OK, I'm going to pick 2 nits here... 1 - re:

The syntax of a Vlookup is: =VLOOKUP( value, table, index_number, [not_exact_match] )Actually, The syntax of a VLOOKUP is:

VLOOKUP(

lookup_value, table_array, col_index_num, [range_lookup])I point this out because the argument names that the always helpful Mike used are not the argument names you will find when researching VLOOKUP on the web or in the Excel Help files. Using argument names other than those specified by the authors of Excel can lead to confusion. e.g. I can Google

col_index_numberand get pages and pages of hits related exclusively to VLOOKUP. However, if I Googleindex_number, the Excel related hits are few and far between.2 - re:

Your table is: @SparePartParticulars

It should be two or more columns and is the range of cells that contains the data your looking for. Something like: B1:C20Let me start by saying that Yes, in the case of the OP's formula where the

col_index_numis specified as 2, thetable_arraymustbe 2 or more columns. No question.(Mike: If your intention was to point out that the

table_arrayshould be 2 or more columns for this specific question, then of course you are 100% correct.)However, just to be clear, there is no requirement that a VLOOKUP

table_arraycontain 2 or more columns. A single column will work just fine as long as thecol_index_numberis also 1. There are times when VLOOKUP might be used to simply determine if a value exists in a column or to actually return a value from a single column.In other words the

table_arrayargument needs to be a range that containsat least as many columnsas thecol_index_numin order to avoid a #REF error.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History