+ Reply to Thread
Results 1 to 15 of 15

Using Index and Match

  1. #1
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    29

    Using Index and Match

    Hi,

    I have attached a sample file to get the idea of what I am trying to accomplish.

    In Column A there are Cities and in Column B there are product types.

    I have created drop downs for the cities and product type. I want to enter a formula next to the drop down that will calculate the Final Map cost based on the city and product type in the data. I will also need to do the same for the data in the building column.

    Is there a way for me to do this? I don't think VLookup works, but I thought maybe Index Match could do the trick. I have limited excperiance with those functions so I am not sure if that will work or if there is another trick that I am unaware of that will work.




    Thank you,
    Attached Files Attached Files

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: Using Index and Match

    Try this array formula**:

    =INDEX(C2:C17,MATCH(1,(A2:A17=B21)*(B2:B17=C21),0))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    You would use the same basic formula for the building, just change the range reference.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    '16 PC & '11 Mac
    Posts
    1,798

    Re: Using Index and Match

    You could try this ARRAY formula in D21:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then this in E21:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Remember, ARRAY formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    - Moo

    ** Just noticed Tony posted this at the same time. GMTA

  4. #4
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,032

    Re: Using Index and Match

    In D21:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In E21

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    '16 PC & '11 Mac
    Posts
    1,798

    Re: Using Index and Match

    Nice use of a non-array formula, hoyasaxa215.

    - Moo

  6. #6
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Using Index and Match

    adding another index to avoid CTRL+SHIFT+ENTER is still an array formula as it's been explained to me (it behaves the same way, same calculation speeds, even worse off in some cases). We could use a helper column to avoid array formulas, but the sample data doesn't suggest that would be beneficial.

    try this in D21 and drag it to E21:

    Please Login or Register  to view this content.
    Entered with CTRL+SHIFT+ENTER
    Last edited by TheN; 01-11-2017 at 06:01 PM.

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    '16 PC & '11 Mac
    Posts
    1,798

    Re: Using Index and Match

    @TheN... Hoyasaxa215's formula works as non-array... tested it and no need for Ctrl+Shift+Enter.

    - Moo

  8. #8
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Using Index and Match

    I know, notice the double index though.

    =INDEX($C$2:$C$17,MATCH($B$21&$C$21,INDEX($A$2:$A$17&$B$2:$B$17,0),0))

    As it has been explained to me (by someone else on this forum) that doesn't really benefit at all from an array formula other than avoiding the need to press CTRL+SHIFT+ENTER. It still functions in much the same way as a normal array formula. I could try digging up the thread, will post link here when I find it if you are interested.

    It was posted by XOR LX somewhere, but I found an article by XOR LX on the exact thing instead, so here you go:

    https://excelxor.com/2014/09/01/inde...-cse-formulas/
    Last edited by TheN; 01-11-2017 at 06:15 PM.

  9. #9
    Forum Expert
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    2,036

    Re: Using Index and Match

    Or ...

    =LOOKUP(2,1/($A$2:$A$17=$B21)/($B$2:$B$17=$C21),C$2:C$17)

    Copy cross.

  10. #10
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Using Index and Match

    Now that's a legitimate non-array formula, well done.

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,892

    Re: Using Index and Match

    The syntax used in post #2 and post #3 is the most efficient.

  12. #12
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    29

    Re: Using Index and Match

    Hi,

    Thank you all for your help. I tried out one of the formulas and it works!

    I do have one question. I would like to be able to have the data on a separate sheet and Hide it so the end user is only using the drop down box and does not see the data. I tried to use the same format of the formulas and have them reference another sheet, but I am getting a #N/A.

    Is there a way to do the same thing but referencing the data on another sheet?


    Thank you!

  13. #13
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    '16 PC & '11 Mac
    Posts
    1,798

    Re: Using Index and Match

    Using the ARRAY formula Tony and I posted earlier it works fine, so long as you update the Sheet references... see my attached workbook...
    - Moo
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    29

    Re: Using Index and Match

    Thank you Moo!

  15. #15
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    '16 PC & '11 Mac
    Posts
    1,798

    Re: Using Index and Match

    I see you've marked the thread as Solved.. GREAT! We're glad to help. If you wish to thank the friendly folks here at Excel Forum for their efforts, feel free to click on the 'Add Reputation' link at the bottom of our posts to give feedback.

    Thanks,
    Moo

+ 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