• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

XLOOKUP: Issue with IF statement and XLOOKUP|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / XLOOKUP: Issue with IF statement and XLOOKUP|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…XLOOKUP: Issue with IF statement an…
sp_PrintTopic sp_TopicIcon
XLOOKUP: Issue with IF statement and XLOOKUP
Avatar
Doug Richards
Member
Members
Level 0
Forum Posts: 19
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
1
December 1, 2020 - 11:45 am
sp_Permalink sp_Print

I have a data table (1) that requires pulling data from another table(2) to autofill some cells, I'm trying to use XLOOKUP to do this with an IF statement.

The data table will have multiple like F1 entries.  I have to track two different sub-projects that make the final product, which each are independent of each other for timeline/amount, but influence the final outcome.

End result will be some form of Dashboard that will show the progress of products through phases in the pipeline timeline to a predicted market introduction time/amount.  This is done by comparing the predicted to the estimated/actual completions of each phase as data is collected during and end of each phase.

I'm still not sure if I have the correct data structure, I'm starting out doing this with manual entries, then hope to expand it to link to each department and have a final table that will auto-populate and the dashboard will be live.  That's my hopes, it all begins with data structure and collection.

Thanks for any help

PS: I'm in the agriculture seed industry and the pipeline process starts in the research department and spans all departments until market introduction of a product.

 

I've attached a couple of jpg that might help.LOOKUP_Data-table.JPGImage Enlarger

LOOKUP_Hybrid-table.JPGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage LOOKUP_Data-table.JPG (76 KB)
  • sp_PlupImage LOOKUP_Hybrid-table.JPG (54 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
December 1, 2020 - 12:54 pm
sp_Permalink sp_Print

Hi Doug,

Can you please supply your workbooks so we don't have to recreate everything.

The IF in your 2nd image doesn't have any logical test?  And I can't tell what cell that formula is in.

regards

Phil

Avatar
Doug Richards
Member
Members
Level 0
Forum Posts: 19
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
3
December 8, 2020 - 6:36 am
sp_Permalink sp_Print

Thank you for your help, sorry for not getting back to you sooner, my company is going through a complete global ERP change over, it has been keeping me quite busy.

I would like to give some background on my situation, it may or may not be unique.  I stated in my previous post that I work for an agricultural seed company that through research and production of seed when then sell to farmers.  I have been with my company for 21+ years working in several different roles from research to manufacturing and the one thing we haven't had was a true product pipeline process.  most departments work in silos and information and data was restricted.  January 1st of 2020 there was a change in upper management's decision to get a product pipeline process in place, which I was given the task.   Until we have all of the data accessible and structured in a way that it is useable, I will have to manually keep the pipeline timeline updated, but they would like to have a dashboard showing the products, their progress through the timeline and weather or not they are going to be on time.

Since we work mostly with hybrid (requiring both male and female) plant, the process has to keep track of mainly two aspects of the three components, the hybrid and the two parents.  Will the final product meet the desired market introduction date and volume.

It's not a difficult process, just hard to describe and it has many variables that change outcomes throughout the process. The process has 5 phases, starting from research through introduction sales.   Each product is independent from each other, not like making the same bolt over and over again, but the process of advancing them is basically the same but the outside influences are very fluid (environment, disease, pest, human error), which can occur from planting to seed inventory.

To accomplish these task, a lot of data is collected that aids in day to day decisions, but for my process I only need relatively few data points from each phase but I have to compare it back to a model which estimates what is required for parents and hybrid are to meeting the expected timeline and volume

With all that said, here is what I am doing at this point (right or wrong):

New product is released from research, I input it into a model with year of sales introduction and volume, the model ("Crop Plan") will then backwards calculate what each phase will need to produce of the parents and when (some of this is a manual input), final phase is the production of the hybrid.

Once the product starts down the process road, I track two data flows (estimated and actual), the "estimated", which is what the department believes it the timeline and volume will be at handoff to next phase, this is compared to the "Crop Plan" numbers, if timeline is extended or volume is lower, then the health indicator is changed.   This "estimated" data can be changed during the process, but most of the time, only once or twice.

The other flow is the "actual", this is the actual volume and date of the product, this is at the harvest end of each process and this will override the estimate data and will adjust the timeline and volume.

As we start a process, the "Risk Level %" of meeting the "Crop Plan" is very high and our "Confidence Level %" is very low, as we fill in "actual" data points, "Risk Level %" will start to lower and "Confidence Level %" will start to increase.

The dashboard will show basically very few data points, Hybrid, Sales Introduction Year, Sales Introduction Volume, Health status.

I have been racking my brain on how to make the data structure that will allow me to make the "Crop Plan" and then collect the data for the "Estimated" and "Actual", then compare the "Crop Plan" to them.  This complete process can take upto 2-5 years.

 

I have attached my thoughts, but stuck on how to proceed and have many questions, am I structured correctly? do I use Power Query?

I have been watching a lot of Youtube video's on Excel and PowerBI, Gantt charts? most of them deal with sales data files and I'm having a hard time making the transition.

I know this is gone past the XLOOKUP question, but I appreciate any help and understand if you don't have the time to do so.  

Thank you in advance.

Doug

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
December 8, 2020 - 12:10 pm
sp_Permalink sp_Print

Hi Doug,

No files were attached. Please upload again and be sure to click the 'Start Upload' button after selecting your file.

Mynda

Avatar
Doug Richards
Member
Members
Level 0
Forum Posts: 19
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
5
December 9, 2020 - 12:53 am
sp_Permalink sp_Print

Don't know what happen, it showed the attachment, let me try again.

I see what I did, I dragged it over but did not upload it.  Sorry.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
December 9, 2020 - 1:25 pm
sp_Permalink sp_Print

Hi Doug,

Thanks for sharing your file. First, XLOOKUP; I don't see any issues with the XLOOKUP fomulas, so I'm not sure what your question is there.

In regards to data structure, the CropPlan data is in a tabular layout, so it looks fine for building dashboards from. I guess the next step is to either add the actual data to this table (using a different data type in column E for Crop Actuals), or build a separate equivalent table that tracks the actual data. If you choose the latter you will need to use Power Pivot to build a model that uses relationships between tables to compare the plan vs actual data.

Once you have some actual data in the file you can start to build your charts.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Catalin Bombea, Lynnette Altomari, Roy Lutke, Dieneba NDIAYE, Tucker Oakley, Natasha Smith
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 202
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27244

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.