Return Info After Meeting 2 Criteria

[sabunabu]

I have a chart that looks like the below:
50% 60% 70%
Multi 140 150 160
Retail 145 155 170
Comm 155 165 180

What I want to do, is create a formula that will search the first column (Multi, Retail, Comm) and then search the top row (50%, 60%, 70%) and then return the number that matches both. For example, if I had to find what retail and 60% was, it would search for those 2 criteria and then return the number 155. How can I accomplish this?

[NBVC]

Assuming your table is in A1:D4,

try:

=INDEX(A1:D4,MATCH(F1,A1:A4,0),MATCH(F2,A1:D1,0))

where F1 houses Retail and F2 house 60%, your input fields

[jsniderfamily]

60 70 80
red 1 2 3
yellow 4 5 6
blue 7 8 9

=INDEX(B2:D4,MATCH("yellow",A2:A 4,0),MATCH(70,B1:D1,0))

returns 5

[sabunabu]

Thanks but I am getting an n/a error. here is my formula:

=INDEX(Treasuries!J6:N10,MATCH(AA9,Treasuries!J6:N10,0),MATCH(AB9,Treasuries!J6:N10,0))

J6:N10 is my table area (similar to A1:D4 posted above)

AA9 is where Retail is and AB9 is where 60% is. What am I doing wrong?

TIA

[NBVC]

Should be:

=INDEX(Treasuries!J6:N10,MATCH(AA9,Treasuries!J6:J10,0),MATCH(AB9,Treasuries!J6:N6,0))

[sabunabu]

That's awesome. Thank you!

[sabunabu]

Actually ran into a small problem, what happens if my % that I'm trying to "match" isn't 50%, 60%, or 70%? Is there a way of saying, if less than 50%, take the 50% number, if less than 60%, take the 60% number, etc?

Sorry for another question

[NBVC]

Try instead:


=INDEX(Treasuries!J6:N10,MATCH(AA9, Treasuries!J6:J10,0),MATCH(TRUE,Treasuries!J6:N6>=AB9,0))

NOTE: This formula must now be confirmed with CTRL+SHIFT+ENTER keys not just ENTER... you'll see { } brackets appear around the formula

[sabunabu]

Interesting with the CTRL+SHIFT+DELETE.... never done that before. When I made this change, it returned Retail instead of the number. Did I do something wrong?

=INDEX(Treasuries!$L$6:$P$10,MATCH(PROPAdjs!AA9,Treasuries!$L$6:$L$10,0),MATCH(TRUE,Treasuries!$L$6:$P$6>=AB9,0))

[NBVC]

it's actually CTRL+SHIFT+ENTER not DELETE

also...

You interjected another sheet name... should that apply at the other Match too?

Like so:

=INDEX(Treasuries!$L$6:$P$10,MATCH(PROPAdjs!AA9, Treasuries!$L$6:$L$10,0),MATCH(TRUE, Treasuries!$L$6:$P$6>=PROPAdjs!AB9,0))

[sabunabu]

Sorry, I meant Enter and not delete. I don't think it matters b/c both matches are pulling from the same page. I changed the second one anyways and it still came back as Retail instead of the number.

[NBVC]

can you attach the workbook here (zipped xl2003 or earlier file please).

[sabunabu]

Unfortunately I cannot as it is sensitive data. It's okay, I can play with it somehow if this way doesnt' work...

Thanks again!

[NBVC]

See if the attached sample will help you to figure it out....


it should work...