vlookup and hlookup formula, a good challenge

[Guest]

to beginu thanks for your help. I need a formula for the following
information. I have a table with the following information:
1 2 3 4
Rose 5% 101 102 103 104
Rose 10% 201 202 203 204
Rose 15% 301 302 303 304
Jack 3% 401 402 403 404
Jack 6% 501 502 503 504
Jack 9% 601 602 603 604

I need a formula that when I request "Rose @ 15% @ 2" My answer is 302

Hope you can understand, Thanks

Frances

 

[T. Valko]

Try this.

Table in the range A2:F7

A10 = Rose
B10 = 15%
C10 = 2

=SUMPRODUCT(--(A2:A7=A10),--(B2:B7=B10),INDEX(C2:F7,,C10))

 

[Guest]

If your table is in A1:F7

A11=Rose
B11=15%
C11=2

you could try
=MAX((A2:A7=A11)*(B2:B7=B11)*(C1:F1=C11)*C2:F7)

array entered. After typing in the formula hold Control+Shift keys and hit
enter.

 

[Guest]

Assume your table A1:F7

=INDEX(A1:F7,MATCH(1,(A1:A7="Rose")*(B1:B7=15%),0),MATCH(2,A1:F1,0))

ctrl+shift+enter, not just enter

 

[Guest]

Another way...

=SUMPRODUCT((A2:A7="Rose")*(B2:B7=15%)*(C1:F1=2)*C2:F7)

 

[Ron Coderre]

If you really want to use "Rose @ 15% @ 2" as the basis for your
lookup...then
try this:

With your posted data in A1:F7

and
A17: Rose @ 15% @ 2

This regular formula:
B17: =INDEX(C1:F7,MATCH(SUBSTITUTE(LEFT(A17,SEARCH("%*@",A17)-1),"
",""),INDEX(TRIM(A1:A7)&"@"&(B1:B7*100),0),0),--RIGHT(SUBSTITUTE(A17,"@",REPT("
",99)),99))

returns: 302

Other examples:
A17: jack@3%@2
returns: 402

A17: jack @ 3% @ 3
returns: 403

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)