+ Reply to Thread
Results 1 to 6 of 6

Index match - column and row (vertical / horizontal) match - equal or greater

  1. #1
    Registered User
    Join Date
    11-30-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Index match - column and row (vertical / horizontal) match - equal or greater

    Hi,

    I was wondering whether someone can help me out?

    I am trying to index and match (not sure whether this is correct to begin with) a column and row to find out what the intersect would be.

    If the Month stated is not in the table then go up to the next month and select that score. e.g Month: 13 not there so go to Month: 24 - if the team then selected was Team 2 the result would be: 6

    If the Month and Team matches select the score that intersects e.g Month 12 and Team 3 = Result: 2

    I have attached a picture which shows desired results and the formula I have built up to now.

    I have tried a lot of various formulas but have had no luck. I have also trawled the internet and cant find anything similar to my issue.

    I hope I have supplied enough info for someone to figure my formula problem out.

    Thanks
    Attached Images Attached Images
    Last edited by lukemc11; 12-04-2018 at 04:34 PM.

  2. #2
    Registered User
    Join Date
    11-22-2018
    Location
    Roma,Italia
    MS-Off Ver
    2013
    Posts
    40

    Re: Index match - column and row (vertical / horizontal) match - equal or greater

    Buonasera
    ho messo il nome del Team in K2 il mese in K3 in K4

    =SCEGLI(SE.ERRORE(SE(CONFRONTA(K3;B1:H1;0)>0;1);2);INDICE($B$2:$H$6;CONFRONTA(K2;A2:A6;0);CONFRONTA(K3;B1:H1;0));INDICE($B$2:$H$6;CONFRONTA(K2;A2:A6;0);CONFRONTA(K3;B1:H1;1)+1))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-30-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Index match - column and row (vertical / horizontal) match - equal or greater

    Excel document attached
    Attached Files Attached Files
    Last edited by jeffreybrown; 12-04-2018 at 06:14 PM. Reason: Please do not post needless quotes!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,945

    Re: Index match - column and row (vertical / horizontal) match - equal or greater

    For your first example cell (B13) use the formula

    =INDEX($B$2:$H$6,MATCH(B11,$A$2:$A$6,FALSE),IFERROR(MATCH(B12,$B$1:$H$1,FALSE),MATCH(B12,$B$1:$H$1)+1))
    Bernie Deitrick
    Excel MVP 2000-2010

  5. #5
    Registered User
    Join Date
    11-30-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Index match - column and row (vertical / horizontal) match - equal or greater

    Quote Originally Posted by Bernie Deitrick View Post
    For your first example cell (B13) use the formula

    =INDEX($B$2:$H$6,MATCH(B11,$A$2:$A$6,FALSE),IFERROR(MATCH(B12,$B$1:$H$1,FALSE),MATCH(B12,$B$1:$H$1)+1))
    Wow! It worked.

    Many thanks - and thank you for the speedy reply.

  6. #6
    Registered User
    Join Date
    11-30-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    10

    Re: Index match - column and row (vertical / horizontal) match - equal or greater

    Quote Originally Posted by FioreMatto View Post
    Buonasera
    ho messo il nome del Team in K2 il mese in K3 in K4

    =SCEGLI(SE.ERRORE(SE(CONFRONTA(K3;B1:H1;0)>0;1);2);INDICE($B$2:$H$6;CONFRONTA(K2;A2:A6;0);CONFRONTA(K3;B1:H1;0));INDICE($B$2:$H$6;CONFRONTA(K2;A2:A6;0);CONFRONTA(K3;B1:H1;1)+1))
    Wow! It worked.

    Many thanks - and thank you for the speedy reply.

+ 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. [SOLVED] INDEX/MATCH based on 4 criteria, 2 vertical, 2 horizontal
    By beenbee in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-02-2021, 04:55 PM
  2. Replies: 16
    Last Post: 06-01-2017, 06:01 PM
  3. index match two vertical one horizontal
    By jordycat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-14-2017, 06:48 AM
  4. [SOLVED] From Vertical to Horizontal / Match index?
    By DieterKoblenz in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-18-2015, 08:35 AM
  5. Replies: 3
    Last Post: 11-06-2015, 07:24 AM
  6. [SOLVED] Transposing data from vertical to horizontal (INDEX/MATCH)
    By johnmitch38 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2015, 07:05 PM
  7. Index Match with Horizontal and Vertical Matches
    By KMCurtis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2014, 04:18 PM

Tags for this Thread

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