Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and Hlookup help
I have a situation where I need to lookup Product A but then lookup Company A
in a column that changes. vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to get this to work. The vlookup area has Product A listed 5 times but Company A (horizontally is only listed 1 time). I hope this makes sense but I can't seem to get it to work. Example - my total for Product A and Company A should be $500 Company A Company B Company C Product A 0 100 0 Product A 0 100 0 Product A 0 100 0 Product A 100 100 0 Product A 100 100 100 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and Hlookup help
Try the below.Change the text strings to cell references...
=SUMIF(A:A,"ProductA",OFFSET(A:A,,MATCH("Company A",A1:J1,0)-1)) OR =SUMIF(A:A,"ProductA",OFFSET(B:B,,MATCH("Company A",A1:J1,0)-2)) "Nikki" wrote: I have a situation where I need to lookup Product A but then lookup Company A in a column that changes. vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to get this to work. The vlookup area has Product A listed 5 times but Company A (horizontally is only listed 1 time). I hope this makes sense but I can't seem to get it to work. Example - my total for Product A and Company A should be $500 Company A Company B Company C Product A 0 100 0 Product A 0 100 0 Product A 0 100 0 Product A 100 100 0 Product A 100 100 100 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and Hlookup help
I have separate tabs and the named range for my data to lookup is Company07.
I can get the Hlookup to work but it will not total the five lines for Product A. "Jackpot" wrote: Try the below.Change the text strings to cell references... =SUMIF(A:A,"ProductA",OFFSET(A:A,,MATCH("Company A",A1:J1,0)-1)) OR =SUMIF(A:A,"ProductA",OFFSET(B:B,,MATCH("Company A",A1:J1,0)-2)) "Nikki" wrote: I have a situation where I need to lookup Product A but then lookup Company A in a column that changes. vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to get this to work. The vlookup area has Product A listed 5 times but Company A (horizontally is only listed 1 time). I hope this makes sense but I can't seem to get it to work. Example - my total for Product A and Company A should be $500 Company A Company B Company C Product A 0 100 0 Product A 0 100 0 Product A 0 100 0 Product A 100 100 0 Product A 100 100 100 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and Hlookup help
Nikki,
Another solution: In the cell for the total of "Product A" from "Company A" type this: "=IF(B1="Company A",SUMIF(A2:A25,"Product A",C2:C25),0)" without the beginning " and ending ". This is with Column A filled with Product A, cell B1 has Company A in it. Cells B2:B6 has 0, 0, 0, 100, 100. Cells C2:C6 has 100, 100, 100, 100, 100. Cell D1 has Company B in it. Cells D2:D6 has 0, 0, 0, 0, 100. Cell C1 has Comapny C in it. Cells C2:C6 are empty. hth "Jackpot" wrote: Try the below.Change the text strings to cell references... =SUMIF(A:A,"ProductA",OFFSET(A:A,,MATCH("Company A",A1:J1,0)-1)) OR =SUMIF(A:A,"ProductA",OFFSET(B:B,,MATCH("Company A",A1:J1,0)-2)) "Nikki" wrote: I have a situation where I need to lookup Product A but then lookup Company A in a column that changes. vlookup("Product A",A1:T25,hlookup("Company A",A1:T25,0)) but can't seem to get this to work. The vlookup area has Product A listed 5 times but Company A (horizontally is only listed 1 time). I hope this makes sense but I can't seem to get it to work. Example - my total for Product A and Company A should be $500 Company A Company B Company C Product A 0 100 0 Product A 0 100 0 Product A 0 100 0 Product A 100 100 0 Product A 100 100 100 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup or Hlookup | Excel Discussion (Misc queries) | |||
VLOOKUP and HLOOKUP | Excel Worksheet Functions | |||
Hi.. not sure if need hlookup or vlookup or something else | Excel Discussion (Misc queries) | |||
VLookup - HLookup | Excel Discussion (Misc queries) | |||
vlookup & hlookup | Excel Worksheet Functions |