Offset Hlookup result

[Dianne]

How can I offset Hlookup result or another formula to
return the values in columns B (sum of A for Name1) or
Columns D (sum of C for Name2) without having to insert
headings into B and D? the result I would like to see:

the result of HLOOKUP(Name1,Data,5,FALSE) on offset
one column to the right = $950 (ref B5) on Sheet 2.

Sheet 1:

A B C D
Name1 Name2
100 150
250 300
600 400
950 850

Thank you in advance.

Dianne

 

[Leo Heuser]

Dianne

One way:

=OFFSET(Data,4,MATCH("name1",Data,FALSE),1,1)

 

[Peo Sjoblom]

You can't you have to use another formula


=HLOOKUP(Name1,Data,5,0)

can be replaced by

=INDEX(Table,5,MATCH(Name1,INDEX(Table,1,),0)+1)

+1 will offset the lookup with one column to the right

 

[diane]

Thank You!

-----Original Message-----
You can't you have to use another formula


=HLOOKUP(Name1,Data,5,0)

can be replaced by

=INDEX(Table,5,MATCH(Name1,INDEX(Table,1,),0)+1)

+1 will offset the lookup with one column to the right


--

Regards,

Peo Sjoblom





.

 

[Guest]

Thank you!

-----Original Message-----
Dianne

One way:

=OFFSET(Data,4,MATCH("name1",Data,FALSE),1,1)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dianne" <[email protected]> skrev i en meddelelse


.

 

[Leo Heuser]

You're welcome.

LeoH

Thank you!