+ Reply to Thread
Results 1 to 7 of 7

formula to search a range

  1. #1
    Registered User
    Join Date
    02-14-2008
    Location
    South Africa, Pretoria
    MS-Off Ver
    2010
    Posts
    47

    formula to search a range

    Hi Folks,

    I am in need of your assistance.

    I have a table with columns January to December with rows for the years starting 2012 to 2023.

    At the intersection of the column and rows there is a number.

    What I would like to do is the following:

    If the input month is Jan 2013 then I would like the formula to provide the value found under column (Jan) row (2013) and place that value in the cell?

    I hope this is not too complicated

    I look forward to your replies
    Regards
    Rob

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: formula to search a range

    Hi use this,

    Where $B$1:$M$1 ={"January","February","March","April","May","June","July","August","September","October","November","December"}

    $A$2:$A$10={2012;2013;2014;2015;2016;2017;2018;2019;2020}


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  3. #3
    Registered User
    Join Date
    02-14-2008
    Location
    South Africa, Pretoria
    MS-Off Ver
    2010
    Posts
    47

    Re: formula to search a range

    thanks for the reply,

    but would this formula not just give you the search only for the year 2014 and month Mar?

    If Changed the month to April the formula would then have to be different ?

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: formula to search a range

    or, assuming table in same place as above and search item in N1
    Formula:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by pjwhitfield; 11-15-2014 at 09:08 AM.

  5. #5
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: formula to search a range

    You can directly reflect the cells instead of 2014 & MAR

    =INDEX($B$2:$M$10,MATCH(Z1,$A$2:$A$10,0),MATCH(Z2,$B$1:$M$1,0))

  6. #6
    Registered User
    Join Date
    02-14-2008
    Location
    South Africa, Pretoria
    MS-Off Ver
    2010
    Posts
    47

    Re: formula to search a range

    thanks for the reply, I will try this option

  7. #7
    Registered User
    Join Date
    02-14-2008
    Location
    South Africa, Pretoria
    MS-Off Ver
    2010
    Posts
    47

    Re: formula to search a range

    Thanks pjwhitfield - your help was perfect

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Is there a formula to search a range of cells and add whichever...
    By financeguy2014 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-30-2014, 01:58 PM
  2. Modify this Search Formula to search across a range of cells
    By zicitron in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2013, 08:11 AM
  3. Formula To Search IP Range for Location
    By atnextc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2012, 10:36 AM
  4. Formula to search if a value is in range of 2 columns
    By tvanhooz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2011, 01:15 AM
  5. Search formula referencing range of keywords
    By Malvaro in forum Excel General
    Replies: 2
    Last Post: 07-01-2008, 05:01 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1