+ Reply to Thread
Results 1 to 9 of 9

combination of a Vlookup and If formula

  1. 12-13-2015, 04:00 PM #1
    top_dog
    top_dog is offline
    Registered User
    Join Date
    02-25-2014
    Location
    Auzzie
    MS-Off Ver
    Excel 2010
    Posts
    49

    combination of a Vlookup and If formula

    Hi team

    I am having trouble writing a formula. The way I have tried to approach my problem is using a vlookup and =if but I cant quite get there. What I have is a master sheet with Part A, Part B and a Part C, each of these Parts have there own sheet. I wanting to grab the data from each of their sheets and bring it back to the master sheet. In the master sheet I have a formula in G1 to bring back the current month. I then want to use a formula to match that month against the month in the corresponding part sheet and bring me back their number. I have attached the sheet. I want the formula to bring back the numbers that are in E4 to E6. Bonus points if you can get the previous months numbers but will be happy just to get current


    Thanks for any help!
    Test.xlsx
    Last edited by top_dog; 12-14-2015 at 04:53 PM.
    Reply With QuoteRegister To Reply
  2. 12-13-2015, 04:06 PM #2
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,228

    Re: combination of a Vlookup and If formula

    How do we identify the row associated with your data as I assume there will be other numeric data in the sheets? Are numbers totals?
    Reply With QuoteRegister To Reply
  3. 12-13-2015, 04:10 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: combination of a Vlookup and If formula

    This will do what you want...
    =INDEX(INDIRECT("'"&$C4&"'!D:O"),24,MATCH(F$1,INDIRECT("'"&$C4&"'!D7:O7"),0))

    However, you did not say how to ID which row to use, so I just used 24. If you have "row" headings, we can ID them to pick the riow to return
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 12-13-2015, 04:10 PM #4
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: combination of a Vlookup and If formula

    Unfortunately starting a duplicate thread is not an acceptable alternative to correcting the title of your original thread.

    I will ask a moderator to close this one.

    edit:-

    Hopefully Ford has noted my comment.
    Last edited by jason.b75; 12-13-2015 at 04:13 PM.
    Reply With QuoteRegister To Reply
  5. 12-14-2015, 04:52 PM #5
    top_dog
    top_dog is offline
    Registered User
    Join Date
    02-25-2014
    Location
    Auzzie
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: combination of a Vlookup and If formula

    Thanks Fdibbins, that worked perfect. I have to manually select the row so your formula allows me to do this! There is a title in the row call "total" would tell me row what I need to be in. But what you have done is more than enough, but would be grateful if you added that.

    Sorry Jason, I tried to remove the previous post or change the heading but it said it was locked. Apologise
    Reply With QuoteRegister To Reply
  6. 12-14-2015, 04:53 PM #6
    top_dog
    top_dog is offline
    Registered User
    Join Date
    02-25-2014
    Location
    Auzzie
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: combination of a Vlookup and If formula

    Thanks Fdibbins, that worked perfect. I have to manually select the row so your formula allows me to do this! There is a title in the row call "total" would tell me row what I need to be in. But what you have done is more than enough, but would be grateful if you added that.

    Sorry Jason, I tried to remove the previous post or change the heading but it said it was locked. Apologise
    Reply With QuoteRegister To Reply
  7. 12-14-2015, 05:30 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: combination of a Vlookup and If formula

    @ jason yes I did, thanks

    top_dog, try this...

    =INDEX(INDIRECT("'"&$C4&"'!D8:O50"),MATCH("total",INDIRECT("'"&$C4&"'!C8:C50"),0),MATCH(F$1,INDIRECT("'"&$C4&"'!D7:O7"),0))

    Note that in your summary sheet, you have a real; date (today() ), but in the other sheets you have text Dec, they need to be the same
    Reply With QuoteRegister To Reply
  8. 12-15-2015, 11:53 AM #8
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: combination of a Vlookup and If formula

    Quote Originally Posted by top_dog View Post
    Sorry Jason, I tried to remove the previous post or change the heading but it said it was locked. Apologise
    Maybe it is I who should apologise, top_dog! Given that the title of this thread is not dissimilar to your original thread, and that you have received a reply to your query from on of the most senior members of the forum, I think that my interpretation of the forum rules could be incorrect.

    Perhaps the forum rules should include some examples / clarification of what constitutes an 'anticipated solution' in a thread title?
    Reply With QuoteRegister To Reply
  9. 12-16-2015, 06:02 PM #9
    top_dog
    top_dog is offline
    Registered User
    Join Date
    02-25-2014
    Location
    Auzzie
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: combination of a Vlookup and If formula

    No Problems Jason! This forum is amazing and I want to make sure I follow the rules.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula need: divide and vlookup combination
    By HerryMarkowitz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2015, 02:17 AM
  2. Use combination of IF and Vlookup formula
    By NehaSureka in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-03-2015, 04:23 PM
  3. [SOLVED] Vlookup,Match,Indirect combination formula
    By deepanc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2013, 07:02 PM
  4. Formula problem - combination of IF and Vlookup
    By Bowish in forum Excel General
    Replies: 1
    Last Post: 05-29-2012, 02:45 AM
  5. SUMIFS and VLOOKUP combination across sheets; dragging formula
    By alillian in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-09-2012, 10:21 AM
  6. Help with combination of if and vlookup formula
    By Fbs1960 in forum Excel General
    Replies: 9
    Last Post: 11-19-2011, 10:04 PM
  7. Combination of OFFSET & VLOOKUP formula
    By Polymnia in forum Excel General
    Replies: 7
    Last Post: 08-30-2011, 08:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1