Multiple indexing arrays?

I give up. Here is the logic: If cell y2=1, index/match using column AA to index, column Y to match. If cell y2=2, index/match using column AB to index, column Y to match. Here is the formula:

=IF(Y2=1,INDEX($AA$8:$AA$12,MATCH(Y8,$Y$8:$Y$12,0),IF(Y2=2,INDEX($AB$8:$AB$12,MATCH(Y8,$Y$8:$Y$12,0)))))

Excel "accepts" the formula. When I type 1 in cell y3, it indexes properly. But when I type 2 in cell y3, I get a FALSE. Am I doing something wrong, or can Excel not do what I'm asking it to do?

Thanks (again.... you all are batting 1000 solving my problems.)

That works fine for y2=1. I added a coma and duplicated what you have for y2=2, but all I got were excel error messages. (I tried different combinations of "if"'s and "index"'s, to no avail.

The "index(choose" also works. But... I feel like an idiot. There will be 3 (maybe 4) index's/match's to choose from. Is it still do-able? (I really appreciate this.)

I could only get the equations to work in one dimension. I'm sure the problem lies in my description of what I need. Here is a better way to explain what I am looking for:
A B C D E
$ to invest Short Medium Long Very long
1 10000 100 21 95 108
2 5000 25 77 50 36
3 2500 88 39 12 44
4 1000 38 99 0 3
5 500 50 25 0 2

IF a client has $5000 to invest short term, I send them flyer #25. If they have $1000 to invest very long term, I send them flyer #3. I know how much they have to invest, they tell me how long they want to invest for, and I want Excel to tell me what flyer to send them. Is that just a Vlookup problem?

Sorry... spacing didn't work. Hopeless.

Maybe you have sample workbook? or just see my file for example and tell me if meet your goal..

I KNEW I wasn't crazy. My approach (see first entry in this thread) was 100% valid. You used the same approach. When I looked closely at your equation, I noticed that you had TWO close parenthesis after the first "if," clause and 4 at the end of the equation. My original equation had ONE at the end of the first "if" clause and FIVE at the end of the equation. I changed it to the way your equation looked, and mine WORKED!!!

I responded to this... but it got put at the end of the thread. Just wanted to make sure you knew that I REALLY appreciated your response.

That was intense. First, thanks to all of you. Every formula I got was what I asked for. I just didn't ask right. In the process of figuring out what I needed, I discovered that what I wanted to do would take about 2000 man hours to set up. And I'm not quite that enthusiastic about this project. So... I'll do the quickie workaround. Plan B it is. Thanks again. (Tony... I don't really have any investment flyers... but you probably know that... I am trying to figure out a system to award points in tennis matches... but ... given how I slaughtered my explanations with a simple example... I can only imagine what would have happened if I would have tried to explain the whole tennis thing.....) Anyway... I'll tab this solved and star everyone who helped.

Just an FYI.... Azumi sent me an example of an equation she used. She used the same approach as I did on the very first post on this thread. Drove me crazy... why did hers work and mine not? I did: IF(Y2=1,INDEX($AA$8:$AA$12,MATCH(Y8,$Y$8:$Y$12,0),IF(Y2=2,INDEX($AB$8:$AB$12,MATCH(Y8,$Y$8:$Y$12,0))))) Note: One close parenthesis before the second "if," and five at the end of the equation. She had two and four. I changed mine to 2 and 4, and, wha-la... perfecto. (Breaking my hand patting myself on the back.) (I'm STILL not going to use the damned thing... but at least I got it to work.)