+ Reply to Thread
Results 1 to 11 of 11

If select choose row & if select choose column. Return column & row intersect

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    6

    If select choose row & if select choose column. Return column & row intersect

    Hello,

    I'm more used to java than excel 2010.

    I have been searching for 2 days on google without finding an answer.

    I want to make the following :

    If [drop-down menu "string" is selected] select column of the selected string.
    if [drop-down menu "string" is selected] select row of the selected string.
    Return value of the intersection of the row and column.

    Is it possible ? I feel microsoft excel 2010 is not enough evolved to do that thing.

    Regards,

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: If select choose row & if select choose column. Return column & row intersect

    Do you have two drop-down menus, it's difficult to tell from your description?

    If so then it's probably the INDEX and MATCH functions that you're looking for.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: If select choose row & if select choose column. Return column & row intersect

    Cell and range references are important in Excel formulae. Suppose your drop-downs are in cells B1 and B2, and you want the result in B3, where your data occupies A10:F20 with header labels in B10:F10 and in A11:A20. Then you can use this in B3:

    =INDEX(B11:F20,MATCH(B1,A11:A20,0),MATCH(B2,B10:F10,0))

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    01-17-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: If select choose row & if select choose column. Return column & row intersect

    Yes I use 2 drop-down menu and I have been googling about INDEX and MATCH functions without finding a clear answer
    The drop-down menu are not on the same sheet.

    In sheet 1 user make the choices and select the first drop-down menu, then the second one. It should return the value coming from sheet 2 where the row and column of the selected choices cross.

    In sheet 2 there is like that :
    ........................... Client 1 .................. Client 2 ................... Client 3
    Red .......................... 1 ..........................5 ...................... 3
    Blue .......................... 9 .......................... 2 ...................... 6
    Green ........................ 8 .......................... 7 .......................... 4

    If, on sheet 1, Client 1 and Green are selected, it will indicate 8 in the cell.

    I hope I'm more clear
    Last edited by Tonyoh87; 01-17-2013 at 05:53 AM.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: If select choose row & if select choose column. Return column & row intersect

    You still haven't told us which cells those drop-downs occur in, or how much data you have, so assuming B1 and B2 for your drop-downs as before, then you can amend my formula like this:

    =INDEX(Sheet2!B2:F20,MATCH(B1,Sheet2!A2:A20,0),MATCH(B2,Sheet2!B1:F1,0))

    Note that you will have to change the items coloured red to suit your data.

    Hope this helps.

    Pete

  6. #6
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: If select choose row & if select choose column. Return column & row intersect

    Hi Tonyoh87

    Try:
    =ColNamedrange RowNamedRange

    That is a "space" between the ranges, it will return the intersection value.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  7. #7
    Registered User
    Join Date
    01-17-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: If select choose row & if select choose column. Return column & row intersect

    Great thanks, issue solved
    Have a great day Pete !

  8. #8
    Registered User
    Join Date
    01-17-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: If select choose row & if select choose column. Return column & row intersect

    Hello

    Any idea on how to make a simpler one :

    sheet 2

    A1:B3
    A ..... 100
    B ..... 50
    C ..... 25

    Sheet 1 :
    A1:A2

    in A2 our drop-down menu which is A, B or C,
    In A1 our value corresponding to A, B or C. The value change automatically depending of the choice in A2. If B is selected, A1 will return 50.

    I try with the INDEX function but it seems not appropriate.

  9. #9
    Registered User
    Join Date
    01-17-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: If select choose row & if select choose column. Return column & row intersect

    no clue anyone ?

  10. #10
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: If select choose row & if select choose column. Return column & row intersect

    How about:

    =VLOOKUP(A2,Sheet2!A1:B3,2,FALSE)

  11. #11
    Registered User
    Join Date
    01-17-2013
    Location
    France
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: If select choose row & if select choose column. Return column & row intersect

    Perfect ! Thank you Andrew-R
    Double solved. Love you guys

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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