# Using Index and Match

1. ## 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,

2. ## 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.

3. ## Re: Using Index and Match

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

Then this in E21:
Formula:
`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. ## Re: Using Index and Match

In D21:

Formula:
`Please Login or Register  to view this content.`

In E21

Formula:
`Please Login or Register  to view this content.`

5. ## Re: Using Index and Match

Nice use of a non-array formula, hoyasaxa215.

- Moo

6. ## 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

7. ## Re: Using Index and Match

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

- Moo

8. ## 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/

9. ## 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. ## Re: Using Index and Match

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

11. ## Re: Using Index and Match

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

12. ## 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. ## 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

14. ## Re: Using Index and Match

Thank you Moo!

15. ## 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

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