1

So I have a table which contains companies that a group of people will visit on given dates. I'm trying to make a formula that will have index for the companies and match it for the given date. When dragging down the formula or double clicking it, it should show all companies that will visit on the given date without giving duplicate values and skip cells that don't have a date.

The formula I am using which is giving me duplicates is:

=INDEX(Table1[Company],MATCH(G2,Table1[Date of visit],0))

Table: https://s16.postimg.org/gv0uh4m05/2016_10_25_194940.png

0

You will need an array formula:

=INDEX(Table1[Company],MATCH(1,(Table1[Date of visit]=G2)*(COUNTIF($H$1:$H1,Table1[Company])=0),0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then excel will put {} around the formula.

  • Could you please explain the formula? – Abood Bataineh Oct 25 '16 at 16:15
  • It is very similar to yours, but it adds the check to see if the company name already exists in the output list with the COUNTIF. Both the criteria needs to return true to output a value. Please remember to mark as correct by clicking the check mark by the answer. @AboodBataineh – Scott Craner Oct 25 '16 at 16:18
  • Could you please tell me why you replaced the G2 in the match formula with "1" and what it does? Also, could you explain with the multiplication symbol does? Already checked the check mark, thanks a lot. – Abood Bataineh Oct 25 '16 at 16:25
  • When multiply two Boolean values (TRUE/FALSE) the become there Bit values (1/0 respectively) So when both are TRUE it becomes 1, When either or both is FALSE it becomes 0. So the 1 in the first Criterion is returning the first time that both the Booleans are TRUE. Then the next row since the company exists in the list it will be false when the iterations get to that company and move to the next which is not in the list already. @AboodBataineh – Scott Craner Oct 25 '16 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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