0

I have to search a given date which is either present or not present in a matrix, and where only the columns are specified. I need to go to specific column (name on top of column), and find in the column whether a given date exists.

enter image description here

  • Are you going to create separate table each consisting of 3 rows for each output or you just need to know if a date exists for specified Machine NAme column? Basically is output data a fixed reference (just one Machine Name & lookup date) or a range of cells in a table? Also in your sample data for Machine4 the lookup date is 10-2-2017 which exists in Machine4 column but your expected output is NO. Is this correct? Are you OK with a helper row created at the bottom of table that could help simplify a possible solution? – patkim Oct 4 '17 at 15:19
  • sorry.Made a mistake in my example.. – Alex22 Oct 4 '17 at 16:02
  • <output> match machine 4 if of course YES and <output>Match Machine2 is NO. Referring to your first question: bsasicly this is an condensed example--> the matrix is valid as from row 16 downwards,; these data are in other sheets Yes I go along your suggestion. – Alex22 Oct 4 '17 at 16:08
4

I am suggesting a solution based on functions like VLOOKUP, INDEX, MATCH and OFFSET. In this example data table is in D2:H9 and input/output cells are in L2:L4. L2 & L3 are input Machine Name and date and L4 is the binary output.

Formula in L4 is

=IF(ISERROR(VLOOKUP(L3,INDEX(D2:H2,MATCH(L2,D2:H2,0)):OFFSET(INDEX(D2:H2,MATCH(L2,D2:H2,0)),100,0),1,FALSE)),"NO","YES")

You need to replicate the formula by adjusting the Input Cell Reference if you need to create more output tables, each representing for a Machine.

This function uses OFFSET to go 100 rows below as length of the table from top. Please adjust this value to match or exceed length of your table in question.

If referring from another sheet precede the cell reference by Sheetname! e.g. Sheet1!D2:H2

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.