Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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




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




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




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




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 
Display Modes  


Similar Threads  
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 