Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup and hlookup in the same formula | Excel Discussion (Misc queries) | |||
Vlookup & hlookup | Excel Worksheet Functions | |||
VLOOKUP , HLOOKUP | Excel Discussion (Misc queries) | |||
can i use vlookup and hlookup together | Excel Discussion (Misc queries) | |||
formula using both vlookup & hlookup | Excel Worksheet Functions |