vlookup and hlookup formula, a good challenge
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 |
vlookup and hlookup formula, a good challenge
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)) -- Biff Microsoft Excel MVP "Frances C" wrote in message ... 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 |
vlookup and hlookup formula, a good challenge
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. "Frances C" wrote: 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 |
vlookup and hlookup formula, a good challenge
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 "Frances C" wrote: 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 |
vlookup and hlookup formula, a good challenge
Another way...
=SUMPRODUCT((A2:A7="Rose")*(B2:B7=15%)*(C1:F1=2)*C 2:F7) "Frances C" wrote: 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 |
vlookup and hlookup formula, a good challenge
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) "Frances C" wrote in message ... 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 |
All times are GMT +1. The time now is 04:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com