# Excel, writing a formula that fits these criteria

1. ## Excel, writing a formula that fits these criteria

I would like to start off by apologizing if I did not format this properly, thanks for helping!

I am trying to write a formula that will go through the "dates" and "points" rows on tab 1 and when it finds a date with a corresponding point it will populate that date in tab 2 under the Date column. Simple enough right? Well here's where I'm running into problems, I need a formula to include the following criteria:

1) If the date does not have a corresponding point in the second row on tab 1 it will skip it and move on to the next date to validate it and so on

*note- I also need the Points column on tab 2 to populate with the corresponding date, but I believe a simple HLOOKUP formula will work just fine

2) I would like to have the formula to where it can be duplicated on tab 2 cells A3,A4,A5 and so on, but as an example: if A2 has date of 9/15/2016, when the formula in A3 is checking for dates with corresponding points in tab 1, it will skip the date in tab 2 located in A2 since it's already been used

Below I have attached some pictures I snipped from the excel file I've been playing with. Thank you for any help!!

all.PNG

2. ## Re: Excel, writing a formula that fits these criteria

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: Excel, writing a formula that fits these criteria

Thanks for the walk through on how to upload the file! I have attached it

4. ## Re: Excel, writing a formula that fits these criteria

In G6, enter this array formula:

=IFERROR(SMALL(IF('Tab 1'!\$C\$5:\$H\$5<>"", 'Tab 1'!\$C\$4:\$H\$4), ROW(A1)), "")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Now copy G2 down.

Then in H6, enter this regular formula and copy down:

=IF(G6="", "", HLOOKUP(G6, 'Tab 1'!\$C\$4:\$H\$5, 2,0))

5. ## Re: Excel, writing a formula that fits these criteria

Works like a charm!!! Thank you very much!

6. ## Re: Excel, writing a formula that fits these criteria

@ bordemkills

Check this file
It contains matricial formulas to solve the problem.

Vicho

Edit**
Sorry, I didn't see JBeaucaire's answered before

There are currently 1 users browsing this thread. (0 members and 1 guests)