IF formula Issues

Harvey12

Board Regular
Joined
Feb 23, 2015
Messages
128
Hi All,

I have created a vlookup which looks to pull data from a separate file to the one that the lookup is contained within.

This file has two categories 'Service Manuals' and 'Operator Manuals'. What I am looking to do is rather than use separate tabs for each one, since the format is all exactly the same I want to combine the two.

This may all sound a little confusing so I will try to elaborate. So I want the lookup to look at the part number in question and first look into the other sheet and find pull back model information for Service manuals, if that part number doesn't exist in service manuals (it must be an operator manual) I need it to look in a different tab at operator manuals. How can this be done?

Here is the current lookup I use for SERVICE MANUALS: I need this same formula to then be able to look into a separate tab in the worksheet below(Publicat) to pull back op manual data if not a service manual.
=IFERROR(VLOOKUP(A20,[Publicat.xls]Service!$C:$G,5,FALSE),"RESERVED - Not Translated")

Can someone please assist.

Many thanks
Harvey
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,776
Office Version
365
Platform
Windows
Try this:

=IFERROR(IFERROR(VLOOKUP(A20,[Publicat.xls]Service!$C:$G,5,FALSE),VLOOKUP(A20,[Publicat.xls]Operator!$C:$G,5,FALSE)),"RESERVED - Not Translated")
 

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
2016
Platform
Windows, Mobile
Steve: Why did you use IFERROR "two times" in the formula...?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
serviceoperator
1text12tttext1
2text23tttext2
5text34tttext3
6text45tttext4
8text56tttext5
9text67tttext6
8tttext7
9tttext8
10tttext9
select a part number10
does it exist in service ?no
does it exist in operator ?yes
service
simple formulas detect if part number
is in either or both lists
and then use the "yes" or "no" generated
to pull out the words from the manuals
operatortttext9
would this approach suit ?

<colgroup><col span="2"><col><col><col span="10"></colgroup><tbody>
</tbody>
 

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
2016
Platform
Windows, Mobile
The number "10" you mentioned in above is also the part of "part number" or it belongs to a serial number...?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,776
Office Version
365
Platform
Windows
The first IFERROR will use the 'operator' sheet should the lookup value not be found in the 'service' sheet. The 2nd IFERROR will produce the text statement 'RESERVED - Not Translated' should the lookup value not be found in either sheet (ie both lookups fail)
 

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
2016
Platform
Windows, Mobile
Hhmmm... I'll check this and let you know the outcome :) Many thanks for your explanation...
 

Harvey12

Board Regular
Joined
Feb 23, 2015
Messages
128
Hi guys, Steve many thanks for your explanation that worked first time for me.
 

Harvey12

Board Regular
Joined
Feb 23, 2015
Messages
128
Hi Guys,

In addition to the above - inside that file those manuals (Service / Operator) Can be translated into 33 different languages(not all languages are available for all manuals). As stated each manual has a specific serial number. I need a further if statement to add to the beginning of that to look inside that file at the column named 'Available' to see if that language is available - if it is I need the formula to bring back that specific serial number for that manual. I will explain:

So in file 1 Call it the 'Summary' file. That currently uses this formula to find a part number: =IFERROR(LEFT($C$4,FIND("-",$C$4)-1),"N/a") which looks in file 2 call it 'raw data file'. Then the formula which you helped me with above is used to look at that part number and pull back product information using the formula you stated.

What I need is the formula that uses a part number above (=IFERROR(LEFT($C$4,FIND("-",$C$4)-1),"N/a") ) to look at which languages are available for that specific part number (Part number is in column C and availability of language is in column K). Column K contains specific values in this case they are either 'Available' Or 'No'. So the formula will need to be able to distinguish between the two and then if it is available it needs to pull the specific part number for that language and put that in the column in file 1 where the formula is written.

If this doesn't make much sense please allow me the chance to try to explain further it is hard to explain and since the files are confidential I cannot attach them.

Many thanks
Harvey
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top