VLOOKUP WITH A CRITERIA

"Just noticed something odd in the first formula, when i typed it out it changed from cell values to the cell description "preferred day Johnie" (see screen) i went into the formula again to change it but each time i type "G4" it changes to "preferred day Johnie"again"

This would indicate two things: The Numbers Preference "Use Header names as Labels" is enabled (checked), and columns A and B of your September Hours table have been defined as Header columns (then had the default grey fill removed). With the preference enables, Numbers, where possible will replace the column letter and row number of a cell reference with the labels in the closest header row (Preferred Day) and header column (Johnie). Removing the name in the header row apparently prevents this action, according to your observation.

"The trouble is as you will see in the next screen shot, Rafe in B5 should show up in the Monday column... it doesn't ????"

The formula constructing the index values in column E of my Data table was written for a table with one header row, and the first data to be indexed entered in row 2.

You have added two header rows to your version of the table, but have not modified the formula to accommodate that change.

The index is constructed from three values: The name in 'this row' of column A, the day in 'this row' of column C, and a COUNT of the number of times that name, followed immediately by that day, followed by 'any text or no text at all' ( represented by the wild card "*" ) have occurred above 'this row' in 'this column', +1 to increment the index count to include the value in 'this row'.

In row 2 (the first row containing data, and the first row to contain an index value) of my table, the count was of values in the (one cell) range E$1:E1. As this cell did not contain a value that would match any of the nameday combinations, the first index value was always NameDay1.

As the formula is filled down into the next row, E$1 continues to point to cell E1 as the start of the range to be counted, but E1 increments by one to expand the range to include all rows above 'this row' (now Row 3).

If row 3 contains the same Name and the same Day as Row 2, then the count of that NameDay set in E$1:E2 is now 1, and the 1 added to that count by the formula makes the index value in E3 ('this row') NameDay2

On your table, where the first data is in Row 4, the single cell range E$1:E1 will not match any of the NameDay combinations. Nor will the E$1:E2 or the E$1:E3 range. Hence the two occurrences of MayaraMonday1, and the missing values in the Rota table.

If your data starts in row four, the initial count of matches must finisn with the cell in row 3 of that column, not row 1.

Change the first argument of COUNTIF from I$1:I1 to I$1:I3 or to I$3:I3

Regards,

Barry

Hi Chris,

INDEX(September Hours::B,MATCH(A$1&B$1&ROW()−1,HIDDEN,0))

"I can't see what the issue is but interestingly the returned error, says it can't find the value "mayaramonday1"

I am assuming i have set the formula to hunt in the wrong place for the data"

EDIT: Noticed on looking again at the actual error message:

…the space between 'MayaraMonday' and '1'. As you've also discovered, that difference is why MATCH cant find the value.

END EDIT

That the error message (in your first image) quotes the correct 'search-for' string indicated the 'A$1&B$1&ROW()−1' part of the formula is working correctly in this cell. As you fill the formula down the column, that part will continue to work correctly, as the references to A1 and B1 both have the absolute reference operator ( $ ) applied to the row, keeping the reference to row 1 as in each row's iteration of the formula. I put the A1 reference in bold a the reference to that cell must also preserve the column reference as the formula is filled right into new columns. Change A$1 to $A$1.

As a general rule, I keep the Numbers preference setting "Use Header names and labels" turned off (unchecked), especially when developing formulas. I'm more comfortable seeing the address of the row, column or cell than having to look to the column and row headers to see if the formula is addressing the correct place. I'd also suggest leaving all coluns visible while developing, testing or trouble-shooting the formula(s).

When you uncheck that preference setting, does the formula show the letter of the column containing the 'search-for' string quoted by the error message? If not, fix that reference (and apply the absolute reference operator to the column to keep the formula searching in that column as the formula is filled right into the Tuesday, Wednesday, etc. columns.

Regards,

Barry

What are the results you want to see?

SG

Hi Chris,

The LOOKUP functions (including MATCH) will return only the result associated with the first occurrence of the search value they are looking for.

Your formula: VLOOKUP(A$1,September Hours::$A4:$E4,2,FALSE)

expressed in English: Get the value in A1 of 'this table',

Search for it in the first column of the cells A4 to E4 of September Hours,

If found, return the value from the second column of A4 to E4,
Accept only an exact match.

Your formula defines a lookup table of only four cells. The search is confined to cell A4 of September Hours. The return value can come only from B4 of that table. If an exact match of the search-for value is found in A4, then the formula will return the value from B4. If not, the formula will return a 'can't find it' error message.

When filled down, the formula will continue to get the search for value from cell A1 of 'this table', but will again search only one cell of column A of September Hours for a match, and return only the value in the same row of column B.

Assuming the bottom table is the one containing the formula, and the top table in your post is September Hours, the formula you present must be as it appears in B4 of the bottom table, where it returns "Katy", the name in B4 of the upper table.

VLOOKUP (and the other lookup functions) can search for only a single value, so asking it to find the customers by Cleaner AND Preferred Day requires combining those two values into a single search-for value.

VLOOKUP (and the other lookup functions) will return values from only the row in which they first find a match for the search-for value.

To find multiple occurrences of the same value, as in his case, requires producing a list of 'index values' marking each occurrence of each value with a distinct value.

VLOOKUP is restricted to using the first (leftmost) column of the lookup table as its search-where location. So using VLOOKUP means the matches for the combined search-for values must be in the first column of September Hours, if VLOOKUP is to be used.

Other functions in the lookup family do not share this limitation, and my own choice would be to use the MATCH and INDEX pairing for that reason.

Here is a copy of your September Hours table (renamed 'Data' only to shorten the display of the formula).

The formula shown is entered in E2, then filled dows to the end of that column.

The index list it creates is a set of text strings combining the cleaner name, the preferred day and the count of that combination to 'this row' in the table.

The second table, Summary, collects a summary list of the customers of a single cleaner (named in A1) for each day.

The Test version of this table contains no error trap, so it displays the expected error messages in cells where there is no match for the search-for value in the index column above.

The formula is entered in B2, then filled down to the end of column B and right to column D.

After determining that no unexpected errors were present, an error trap was added to the formula to trap the error messages and place a null string in the cells containing those errors.

The new formula is filled right to column C and down to the last row of the table, replacing the previous version.

Column E of the Data table should be hidden. It's not needed by viewers of the table, and hiding it will help protect the formula it contains.

Regards,

Barry