Hello to one and all.
I am trying the following link results and its not showing anything for me. https://discussions.apple.com/thread/7733907
In Col B is a formula VLOOKUP($A2,LOG::A:E,5,1)
Thank you for the help.
iMac 21.5″, macOS 10.13
Formula below first table:
This tells Numbers to look for the value in cell A2 in column A of a table named "LOG" and to return the value from the cell on the same row of the fifth column of that table. The 1 tells VLOOKUP to accept the largest value that is less than or equal to the value being looked for.
Without seeing the table named "LOG", I would say that the cell in column E of LOG that is on the same row as the date and time value Fri, 1 Oct 2021 is in column A of that table is empty.
If there is nothing in that cell, Numbers will interpret that as a numerical value of zero.
If there is a text value, such as a null string ( "" ) or a space ( " " ) in the cell in column E, that text will be copied to the cell containing the formula, and the cell will appear to be empty.
The text value 'space character' can be inserted in the cell by placing the insertion point in the cell and typing a space. The null string can be inserted by pressing = to open the formula editor, then typing two double quote marks as the formula.
Your second formula, shown below the second image of the table is taken from Wayne Contello's post in November of 2016, answering a question regarding extracting the distinct names in a list containing several names that appear more than once.
Wayne's formula. as it appears in row 3
Your formula, as it appears in Row 16
Note the row number of the last cell in the ranges of column A and E in Wayne's formula, and compare them with the row number of the cell reference to the single cell in column A.
Note the row number of the last cell in the ranges of column B and E in your formula, and compare them with the row number of the cell reference to the single cell (same colour as the single cell reference in Wayne's version).
Point to note: The "Use header names as labels" is not a good choice when trouble shooting a formula. "Pilot" is the header name for column B. Fri, "15 Oct 2021" is the header name for row 16
Your formula is in row 16. The first range reference tells Numbers to count the number of times the name in rows 1 to 16 of column B match the name in 'this row' of column B. 'This row' is row 16.
IF the count is zero, then the formula looks for the largest number in rows 1 to 15 of column E (the rows above the formula in the column containing the formula), add one to that number and place the result in the cell containing the formula.
Column E on your table is 'empty' because your formula is always counting the number of times the name on THIS ROW appears from the top of column B to THIS ROW of column B.
Since the count includes "THIS ROW" the result will always be at least 1.
Change =0 to =1
OR change the range references to end on the row above the row containing the formula,
and you formula should work.
Regards,
Barry
Formula below first table:
This tells Numbers to look for the value in cell A2 in column A of a table named "LOG" and to return the value from the cell on the same row of the fifth column of that table. The 1 tells VLOOKUP to accept the largest value that is less than or equal to the value being looked for.
Without seeing the table named "LOG", I would say that the cell in column E of LOG that is on the same row as the date and time value Fri, 1 Oct 2021 is in column A of that table is empty.
If there is nothing in that cell, Numbers will interpret that as a numerical value of zero.
If there is a text value, such as a null string ( "" ) or a space ( " " ) in the cell in column E, that text will be copied to the cell containing the formula, and the cell will appear to be empty.
The text value 'space character' can be inserted in the cell by placing the insertion point in the cell and typing a space. The null string can be inserted by pressing = to open the formula editor, then typing two double quote marks as the formula.
Your second formula, shown below the second image of the table is taken from Wayne Contello's post in November of 2016, answering a question regarding extracting the distinct names in a list containing several names that appear more than once.
Wayne's formula. as it appears in row 3
Your formula, as it appears in Row 16
Note the row number of the last cell in the ranges of column A and E in Wayne's formula, and compare them with the row number of the cell reference to the single cell in column A.
Note the row number of the last cell in the ranges of column B and E in your formula, and compare them with the row number of the cell reference to the single cell (same colour as the single cell reference in Wayne's version).
Point to note: The "Use header names as labels" is not a good choice when trouble shooting a formula. "Pilot" is the header name for column B. Fri, "15 Oct 2021" is the header name for row 16
Your formula is in row 16. The first range reference tells Numbers to count the number of times the name in rows 1 to 16 of column B match the name in 'this row' of column B. 'This row' is row 16.
IF the count is zero, then the formula looks for the largest number in rows 1 to 15 of column E (the rows above the formula in the column containing the formula), add one to that number and place the result in the cell containing the formula.
Column E on your table is 'empty' because your formula is always counting the number of times the name on THIS ROW appears from the top of column B to THIS ROW of column B.
Since the count includes "THIS ROW" the result will always be at least 1.
Change =0 to =1
OR change the range references to end on the row above the row containing the formula,
and you formula should work.
Regards,
Barry
Without seeing the Log table it's hard to tell exactly what you are trying to do.
I would keep in mind, though, that Numbers has come a long way since the 2016 thread to which you link. This kind of problem should be much easier. I would look into the XLOOKUP function.
Or even easier, since you don't have to input and debug formulas, consider using a Pivot Table (Organize > Create Pivot Table > On Current Sheet).
SG
A Lookup, which returns an empty cell if empty?
