Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 19
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
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


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default Problem with hlookup formula

On Wednesday, 9 December 2020 at 18:48:40 UTC+2, Claus Busch wrote:
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

Good morning Claus,
I sent you my file yesterday evening but this morning I see it could not be delivered. I used your t-online.de address.
I will try again, now.
Regards, Norbert
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default Problem with hlookup formula

On Thursday, 10 December 2020 at 06:55:17 UTC+2, Norbert wrote:
On Wednesday, 9 December 2020 at 18:48:40 UTC+2, Claus Busch wrote:
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

Good morning Claus,
I sent you my file yesterday evening but this morning I see it could not be delivered. I used your t-online.de address.
I will try again, now.
Regards, Norbert

Sorry Claus, emails are all coming back. I didn't even attached a file, just a dropbox transfer link.
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default Problem with hlookup formula

On Thursday, 10 December 2020 at 08:00:50 UTC+2, Norbert wrote:
On Thursday, 10 December 2020 at 06:55:17 UTC+2, Norbert wrote:
On Wednesday, 9 December 2020 at 18:48:40 UTC+2, Claus Busch wrote:
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

Good morning Claus,
I sent you my file yesterday evening but this morning I see it could not be delivered. I used your t-online.de address.
I will try again, now.
Regards, Norbert

Sorry Claus, emails are all coming back. I didn't even attached a file, just a dropbox transfer link.

Hi Claus, I sent you an email from my private account. Up to now, I didn't receive any error. I hope it went through.
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Problem with hlookup formula

Hi Norbert,

Am Thu, 10 Dec 2020 01:49:52 -0800 (PST) schrieb Norbert:

Hi Claus, I sent you an email from my private account. Up to now, I didn't receive any error. I hope it went through.


the workbook is on the way back to your private account. First I
enlarged the range for "Weekly_Database" to B6:FA505. Then I created the
formula for the customer in cell D8:
=INDEX(WEEKLY_DATABASE,MATCH(B11,INDEX(WEEKLY_DATA BASE,,1),0),MATCH($D$8,INDEX(WEEKLY_DATABASE,1,),0 ))


Regards
Claus B.
--
Windows10
Office 2016
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default Problem with hlookup formula

On Thursday, 10 December 2020 at 13:56:46 UTC+2, Claus Busch wrote:
Hi Norbert,
Am Thu, 10 Dec 2020 01:49:52 -0800 (PST) schrieb Norbert:

Hi Claus, I sent you an email from my private account. Up to now, I didn't receive any error. I hope it went through.

the workbook is on the way back to your private account. First I
enlarged the range for "Weekly_Database" to B6:FA505. Then I created the
formula for the customer in cell D8:
=INDEX(WEEKLY_DATABASE,MATCH(B11,INDEX(WEEKLY_DATA BASE,,1),0),MATCH($D$8,INDEX(WEEKLY_DATABASE,1,),0 ))
Regards
Claus B.
--
Windows10
Office 2016

Thanks Claus,
I was actually looking for the formula for cell E11. I sent you a private email.
Please use attached file, as I made a lot of entries in the mean-time.
Thanks!


  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default Problem with hlookup formula

On Thursday, 10 December 2020 at 15:41:18 UTC+2, Norbert wrote:
On Thursday, 10 December 2020 at 13:56:46 UTC+2, Claus Busch wrote:
Hi Norbert,
Am Thu, 10 Dec 2020 01:49:52 -0800 (PST) schrieb Norbert:

Hi Claus, I sent you an email from my private account. Up to now, I didn't receive any error. I hope it went through.

the workbook is on the way back to your private account. First I
enlarged the range for "Weekly_Database" to B6:FA505. Then I created the
formula for the customer in cell D8:
=INDEX(WEEKLY_DATABASE,MATCH(B11,INDEX(WEEKLY_DATA BASE,,1),0),MATCH($D$8,INDEX(WEEKLY_DATABASE,1,),0 ))
Regards
Claus B.
--
Windows10
Office 2016

Thanks Claus,
I was actually looking for the formula for cell E11. I sent you a private email.
Please use attached file, as I made a lot of entries in the mean-time.
Thanks!

Hi Claus,
I managed to change the formula to work in E11 and to give me the correct answer.
The formula is this:
=INDEX(WEEKLY_DATABASE,MATCH(B11,INDEX(WEEKLY_DATA BASE,,1),0),MATCH($D$8,INDEX(WEEKLY_DATABASE,1,),0 )+1)

But it only worked when I enlarged the range for "Weekly_Database" to B6:FA505. But why would I have to do that? I don't
understand!

  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default Problem with hlookup formula

On Thursday, 10 December 2020 at 15:57:24 UTC+2, Norbert wrote:
On Thursday, 10 December 2020 at 15:41:18 UTC+2, Norbert wrote:
On Thursday, 10 December 2020 at 13:56:46 UTC+2, Claus Busch wrote:
Hi Norbert,
Am Thu, 10 Dec 2020 01:49:52 -0800 (PST) schrieb Norbert:

Hi Claus, I sent you an email from my private account. Up to now, I didn't receive any error. I hope it went through.
the workbook is on the way back to your private account. First I
enlarged the range for "Weekly_Database" to B6:FA505. Then I created the
formula for the customer in cell D8:
=INDEX(WEEKLY_DATABASE,MATCH(B11,INDEX(WEEKLY_DATA BASE,,1),0),MATCH($D$8,INDEX(WEEKLY_DATABASE,1,),0 ))
Regards
Claus B.
--
Windows10
Office 2016

Thanks Claus,
I was actually looking for the formula for cell E11. I sent you a private email.
Please use attached file, as I made a lot of entries in the mean-time.
Thanks!

Hi Claus,
I managed to change the formula to work in E11 and to give me the correct answer.
The formula is this:
=INDEX(WEEKLY_DATABASE,MATCH(B11,INDEX(WEEKLY_DATA BASE,,1),0),MATCH($D$8,INDEX(WEEKLY_DATABASE,1,),0 )+1)

But it only worked when I enlarged the range for "Weekly_Database" to B6:FA505. But why would I have to do that? I don't
understand!

Hi Claus,
I just wanted to thank you again for helping me with my spreadsheet. Your formula saved me a lot of manual work and most
probably prevented me from making mistakes. I could finalise my calculations on time, thanks to you!
Please explain to me why I had to enlarge the range.
  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Problem with hlookup formula

Hi Norbert,

Am Fri, 11 Dec 2020 02:35:04 -0800 (PST) schrieb Norbert:

I just wanted to thank you again for helping me with my spreadsheet. Your formula saved me a lot of manual work and most
probably prevented me from making mistakes. I could finalise my calculations on time, thanks to you!
Please explain to me why I had to enlarge the range.


with the new formula you must not enlarge the range.
With the formula before I refered to WEEKLY_DATABASE and column B was
not included.


Regards
Claus B.
--
Windows10
Office 2016
  #19   Report Post  
Junior Member
 
Posts: 25
Default Below is a few major Check List to check why HLOOKUP is not working

1. Not Having Lookup_Value in First Row
2. Counting the Wrong Number of Rows for ‘Row_index_number’ parameter
3. Extra Space or Characters
4. Not Using ‘FALSE’ for Exact Matching [Range_Lookup]
5. A new row has been inserted in the old table where HLOOKUP was working

Regards,
Jerry
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 03: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 09:09 PM.

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

About Us

"It's about Microsoft Excel"