Solved How to write a formula with IF and vlookup

January 19, 2015 at 06:06:34
Specs: Windows 7
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.

See More: How to write a formula with IF and vlookup

Report •


✔ Best Answer
January 20, 2015 at 13:55:57
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 value is: Hitachi!Summary[@PartNo]

The @PartNo needs to be a specific cell
and if your in the Hitachi workbook you
do not need to include the name in the formula.

Your table is: @SparePartParticulars

It should be two or more columns
and is the range of cells that contains the data your looking for.
Somthing like: B1:C20

Your index_number is: 2

The 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

http://www.skeptic.com/

message edited by mmcconaghy



#1
January 19, 2015 at 07:38:03
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
January 19, 2015 at 17:07:47
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


Report •

#3
January 20, 2015 at 12:45:02
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 formula

What 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

http://www.skeptic.com/


Report •

Related Solutions

#4
January 20, 2015 at 13:55:57
✔ 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 value is: Hitachi!Summary[@PartNo]

The @PartNo needs to be a specific cell
and if your in the Hitachi workbook you
do not need to include the name in the formula.

Your table is: @SparePartParticulars

It should be two or more columns
and is the range of cells that contains the data your looking for.
Somthing like: B1:C20

Your index_number is: 2

The 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#5
January 21, 2015 at 04:09:12
Thanks a lot! I'll try it out and see if it works :)
Bryan

message edited by bryany361


Report •

#6
January 21, 2015 at 06:34:21
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_number and get pages and pages of hits related exclusively to VLOOKUP. However, if I Google index_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:C20

Let me start by saying that Yes, in the case of the OP's formula where the col_index_num is specified as 2, the table_array must be 2 or more columns. No question.

(Mike: If your intention was to point out that the table_array should 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_array contain 2 or more columns. A single column will work just fine as long as the col_index_number is 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_array argument needs to be a range that contains at least as many columns as the col_index_num in order to avoid a #REF error.

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


Report •


Ask Question