Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
HLOOKUP PROBLEM | Excel Worksheet Functions | |||
Problem with HLookup. Not sure if it is a bug or not | Excel Worksheet Functions | |||
hlookup problem | Excel Discussion (Misc queries) | |||
HLookup Problem | Excel Programming | |||
Hlookup Problem | Excel Programming |