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

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

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!

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? .......

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

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"

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

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

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?

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

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

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.

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.

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

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!

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!

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.

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

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