Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 20, 12:27 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
Default Problem with hlookup formula

Hi,
how can I get the value of the neighbouring cell of the cell which is found by
a hlookup formula returned.
E.g: my hlookup formula finds the cell C372 but I want the value of D372

  #2   Report Post  
Old December 9th 20, 12:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2016
Posts: 17
Default Problem with hlookup formula

On 09/12/2020 11:27, Norbert wrote:
Hi,
how can I get the value of the neighbouring cell of the cell which is found by
a hlookup formula returned.
E.g: my hlookup formula finds the cell C372 but I want the value of D372


You need the OFFSET function

--
Adrian C
  #3   Report Post  
Old December 9th 20, 12:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
Default Problem with hlookup formula

On Wednesday, 9 December 2020 at 13:36:12 UTC+2, Adrian Caspersz wrote:
On 09/12/2020 11:27, Norbert wrote:
Hi,
how can I get the value of the neighbouring cell of the cell which is found by
a hlookup formula returned.
E.g: my hlookup formula finds the cell C372 but I want the value of D372

You need the OFFSET function

--
Adrian C


Thanks Adrian! Never used Offset before, but it makes sense. Cool!
  #4   Report Post  
Old December 9th 20, 01:12 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
Default Problem with hlookup formula

On Wednesday, 9 December 2020 at 13:50:26 UTC+2, Norbert wrote:
On Wednesday, 9 December 2020 at 13:36:12 UTC+2, Adrian Caspersz wrote:
On 09/12/2020 11:27, Norbert wrote:
Hi,
how can I get the value of the neighbouring cell of the cell which is found by
a hlookup formula returned.
E.g: my hlookup formula finds the cell C372 but I want the value of D372

You need the OFFSET function

--
Adrian C

Thanks Adrian! Never used Offset before, but it makes sense. Cool!


Thats my hlookup formula:
=(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE) it works! but I need the value of the next column

and that's what I tried, but doesn't work:
=offset(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE),0,1)

error message: There is a problem with this formula. Not trying to type a formula? .......
  #5   Report Post  
Old December 9th 20, 01:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default Problem with hlookup formula

Hi Norbert,

Am Wed, 9 Dec 2020 04:12:59 -0800 (PST) schrieb Norbert:

Thats my hlookup formula:
=(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE) it works! but I need the value of the next column

and that's what I tried, but doesn't work:
=offset(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE),0,1)


try:
=INDEX(8:8,,MATCH(D8,WEEKLY_DATABASE,0))


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Old December 9th 20, 01:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
Default Problem with hlookup formula

On Wednesday, 9 December 2020 at 14:26:04 UTC+2, Claus Busch wrote:
Hi Norbert,
Am Wed, 9 Dec 2020 04:12:59 -0800 (PST) schrieb Norbert:

Thats my hlookup formula:
=(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE) it works! but I need the value of the next column

and that's what I tried, but doesn't work:
=offset(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE),0,1)

try:
=INDEX(8:8,,MATCH(D8,WEEKLY_DATABASE,0))


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,
nope, unfortunately it doesn't work. It brings back N/A

I might have to say that my range WEEKLY_DATABASE is on sheet: "weekly"
The formula, I need on sheet: "calculations", cell E11

When I trace Precendents of your formula (which I don't understand at all!!!) I get markers on sheet: "calculations"
(the entire row 8 is framed by a blue frame and there is an arrow from A8 and D8 to my cell with the formula (E11)
and the one for my range: WEEKLY_DATABASE on sheet: "weekly"
  #7   Report Post  
Old December 9th 20, 01:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
Default Problem with hlookup formula

On Wednesday, 9 December 2020 at 14:41:46 UTC+2, Norbert wrote:
On Wednesday, 9 December 2020 at 14:26:04 UTC+2, Claus Busch wrote:
Hi Norbert,
Am Wed, 9 Dec 2020 04:12:59 -0800 (PST) schrieb Norbert:

Thats my hlookup formula:
=(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE) it works! but I need the value of the next column

and that's what I tried, but doesn't work:
=offset(HLOOKUP(D8,WEEKLY_DATABASE,376,FALSE),0,1)

try:
=INDEX(8:8,,MATCH(D8,WEEKLY_DATABASE,0))


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,
nope, unfortunately it doesn't work. It brings back N/A

I might have to say that my range WEEKLY_DATABASE is on sheet: "weekly"
The formula, I need on sheet: "calculations", cell E11

When I trace Precendents of your formula (which I don't understand at all!!!) I get markers on sheet: "calculations"
(the entire row 8 is framed by a blue frame and there is an arrow from A8 and D8 to my cell with the formula (E11)
and the one for my range: WEEKLY_DATABASE on sheet: "weekly"


and the range WEEKLY_DATABASE is: =weekly!$E$6:$FA$505
  #8   Report Post  
Old December 9th 20, 02:39 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default Problem with hlookup formula

Hi Norbert,

Am Wed, 9 Dec 2020 04:58:00 -0800 (PST) schrieb Norbert:

and the range WEEKLY_DATABASE is: =weekly!$E$6:$FA$505


try:
=INDEX(WEEKLY_DATABASE,376,MATCH(D8,INDEX(WEEKLY_D ATABASE,376,),0)+1)


Regards
Claus B.
--
Windows10
Office 2016
  #9   Report Post  
Old December 9th 20, 03:39 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
Default Problem with hlookup formula

On Wednesday, 9 December 2020 at 15:39:42 UTC+2, Claus Busch wrote:
Hi Norbert,
Am Wed, 9 Dec 2020 04:58:00 -0800 (PST) schrieb Norbert:

and the range WEEKLY_DATABASE is: =weekly!$E$6:$FA$505

try:
=INDEX(WEEKLY_DATABASE,376,MATCH(D8,INDEX(WEEKLY_D ATABASE,376,),0)+1)
Regards
Claus B.
--
Windows10
Office 2016

Sorry Claus, still not working. Error: #N/A
The indicator for precedents looks better. Only pointing to the array WEEKLY_DATABASE and D8

If I understand it correctly, Index specifies a cell (by the number for row and column of a certain array)
Ok, the column we don't have, therfore we need the "match" function. The row is actually the 371st row of the range, but
in the Excel spreadsheet it is row no. 376, therefore I think I have to change both 376 to 371.
I tried that, still #N/A

I tried to only find the column number by entering: =Match(D8,WEEKLY_DATABASE,0)+1
Result: #N/A

The content of D8 is actually a formula which returns a name of a person (text)
D8: =VLOOKUP(D7,P8:R59,2,FALSE)

The names are listet in array WEEKLY_DATABASE in its first row (row no.6).

Any further ideas?


  #10   Report Post  
Old December 9th 20, 05:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default Problem with hlookup formula

Hi Norbert,

Am Wed, 9 Dec 2020 06:39:41 -0800 (PST) schrieb Norbert:

If I understand it correctly, Index specifies a cell (by the number for row and column of a certain array)
Ok, the column we don't have, therfore we need the "match" function. The row is actually the 371st row of the range, but
in the Excel spreadsheet it is row no. 376, therefore I think I have to change both 376 to 371.
I tried that, still #N/A

I tried to only find the column number by entering: =Match(D8,WEEKLY_DATABASE,0)+1
Result: #N/A

The content of D8 is actually a formula which returns a name of a person (text)
D8: =VLOOKUP(D7,P8:R59,2,FALSE)

The names are listet in array WEEKLY_DATABASE in its first row (row no.6).


I don't understand your layout. How can you find a value in C376 if
WEEKLY_DATABASE is in range ("E6:FA505")?
Can you provide the workbook?


Regards
Claus B.
--
Windows10
Office 2016


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
HLOOKUP PROBLEM mmcap Excel Worksheet Functions 4 November 27th 08 04:24 AM
Problem with HLookup. Not sure if it is a bug or not rockycho912 Excel Worksheet Functions 3 September 6th 08 06:37 AM
hlookup problem Desperate Excel Discussion (Misc queries) 3 August 14th 08 07:24 PM
HLookup Problem JD McLeod Excel Programming 3 October 5th 07 12:05 AM
Hlookup Problem ExcelMonkey Excel Programming 1 October 14th 05 02:30 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017