A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Vlookup and Hlookup help



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 04:15 PM posted to microsoft.public.excel.misc
Nikki
external usenet poster
 
Posts: 341
Default 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  
Old June 4th, 2010, 04:22 PM posted to microsoft.public.excel.misc
Jackpot
external usenet poster
 
Posts: 28
Default 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  
Old June 4th, 2010, 04:40 PM posted to microsoft.public.excel.misc
Nikki
external usenet poster
 
Posts: 341
Default 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  
Old June 4th, 2010, 04:43 PM posted to microsoft.public.excel.misc
FloMM2
external usenet poster
 
Posts: 189
Default 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 D26 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.