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

January 20, 2015 at 13:55:57

#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 > tagsto align your data.http://www.computing.net/howtos/sho...MIKEmessage 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 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 mixof letters and numbers.If you can post a small sample of your data.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
January 20, 2015 at 13:55:57

Report •

#5
January 21, 2015 at 04:09:12
 Thanks a lot! I'll try it out and see if it works :)Bryanmessage 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: @SparePartParticularsIt 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_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.

Report •