vlookup and hlookup formula, a good challenge

F

Frances C

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

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))
 
J

JMB

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.
 
T

Teethless mama

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
 
R

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top