Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-30-2020, 12:33 PM
andysvision andysvision is offline Help create this formula: Windows 7 64bit Help create this formula: Office 2007
Novice
Help create this formula:
 
Join Date: Jan 2017
Posts: 8
andysvision is on a distinguished road
Default Help create this formula:


Hello guys, Hope everyone is fine and this virus get off soon!

Now basically i need to create a formula that has as main core VLOOKUP (i think so...)
All the instruction about what i need are included in the excel file i sended attached.

Please i cant make this work as i want...

Thnak you in advace!
Attached Files
File Type: xlsx formula.xlsx (20.4 KB, 10 views)
Reply With Quote
  #2  
Old 04-30-2020, 08:32 PM
Guessed's Avatar
Guessed Guessed is offline Help create this formula: Windows 10 Help create this formula: Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

This is harder than it needs to be because your source data is not one list and so you would need to nest different formulas for each month. Also, you haven't specified what happens when a fruit appears in more than one month.

I suggest you reformat your three lists to make a two column table like this
Fruit | Month
Apple | March
Fig | March

You will then find that VLookup becomes a lot easier and you can use a pivot table if you want to display the data like the way you presented it in your sample.

I would also recommend you learn about Index and Match from resources you can find online like this one
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 05-01-2020, 08:14 AM
andysvision andysvision is offline Help create this formula: Windows 7 64bit Help create this formula: Office 2007
Novice
Help create this formula:
 
Join Date: Jan 2017
Posts: 8
andysvision is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
This is harder than it needs to be because your source data is not one list and so you would need to nest different formulas for each month. Also, you haven't specified what happens when a fruit appears in more than one month.

I suggest you reformat your three lists to make a two column table like this
Fruit | Month
Apple | March
Fig | March

You will then find that VLookup becomes a lot easier and you can use a pivot table if you want to display the data like the way you presented it in your sample.

I would also recommend you learn about Index and Match from resources you can find online like this one
I will get the anser only for a specific month so that doesnt make any difference if "apple" is in 3 months or not, because the formula must look for the specific month i put... hope this might get you the best im asking.
Reply With Quote
  #4  
Old 05-02-2020, 06:29 AM
Guessed's Avatar
Guessed Guessed is offline Help create this formula: Windows 10 Help create this formula: Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

OK, if you are changing the Month in E2 and want the calculation in D2 then put this formula in D2
Code:
=IF(COUNTIFS(OFFSET(G1,1,MATCH(E2,H1:L1,0),21,2),B3)>0,"Yes","No")
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #5  
Old 05-02-2020, 07:49 AM
andysvision andysvision is offline Help create this formula: Windows 7 64bit Help create this formula: Office 2007
Novice
Help create this formula:
 
Join Date: Jan 2017
Posts: 8
andysvision is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
OK, if you are changing the Month in E2 and want the calculation in D2 then put this formula in D2
Code:
=IF(COUNTIFS(OFFSET(G1,1,MATCH(E2,H1:L1,0),21,2),B3)>0,"Yes","No")


WORKS PERFECT!!!!

Thank you!

Last edited by andysvision; 05-02-2020 at 11:28 AM.
Reply With Quote
Reply

Tags
excel formula, formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create a simple formula ep2002 Excel 1 09-06-2017 11:00 PM
Help create this formula: Help to create this formula: andysvision Excel 7 01-05-2017 02:35 PM
Help create this formula: How to create mileage formula maxliv Excel 4 08-15-2016 08:13 AM
Help create this formula: Create formula for entire column tdodson Excel 2 10-27-2015 09:38 PM
Please help me create this formula... doublejoy Excel 1 08-22-2009 03:04 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft