Index/match with Data Validation Listing

[rise206]

Hey everyone,
Finished my intermediate excel class and am trying to practice nested index/match, but this time with a Data Validation Listing. I have attached a simple excel sheet.

Basically, I am trying to have the Price show up once I select a drop down animal from the list.

In my Price Cell, I have:

=INDEX(A4:B7,(MATCH(A11,Animal,0)),Animal,TRUE)

Name Manager:
"Animal"--Includes 4 animals

Thanks,
rise

[NBVC]

Try:

=INDEX(A4:B7,MATCH(A11,Animal,0),2)

Also, have a look here for some good reading on the subject:

http://www.contextures.com/xlfunctions03.html

[rise206]

Thanks nbvc, I appreciate the extra info too. It definitely breaks it down a lot easier than my prof did.

[rise206]

Another Question:
When using Index/Match, when would I use the True/False statement for the index function?

[NBVC]

I don't think you use it in the INDEX part.. you could use it in the MATCH function...

e.g. =MATCH(A11,Animal,TRUE) or =MATCH(A11,Animal,FALSE)

where TRUE looks for an approximate match, and FALSE looks for an exact match.

[rise206]

And Finally...

What if I have 3 animals with 1 merged price? I'll only get the price for the first animal of the 3, but the other 2 will be 0.

I have attached it again

Thanks

[NBVC]

The recommendation here is not to use merged cells.. Put the 100 in each corresponding cell.

[rise206]

Darn....thanks!