Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old December 10th 20, 05:55 AM 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 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  
Old December 10th 20, 07:00 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
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  
Old December 10th 20, 10:49 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
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  
Old December 10th 20, 12:56 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 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  
Old December 10th 20, 02: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 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  
Old December 10th 20, 02:57 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 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  
Old December 11th 20, 11:35 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 40
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  
Old December 11th 20, 11:43 AM 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 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  
Old December 31st 20, 09:43 AM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2020
Posts: 26
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 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 08:16 AM.

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