CODEWITHSUNDEEP
excelexcel-formula
Ryan_C
Ryan_C

Reputation: 501

Using Excel to Filter Unwanted Data

I am completely stuck on an issue that I'm afraid has an easy solution, but that I'm over complicating. Hoping to get some new insight here.

I have two columns in Excel, each are formatted as dates. In both column, the dates are reported every 60 seconds; HOWEVER, one of the columns has missing data. I would like to line the columns up, which I thought could be done using "INDEX(MATCH)" but that is getting me no where. See the screenshot:enter image description here

What I'm trying to do in column "F" is line up the values in column "B" with its corresponding date in column "C". Where there is a "#N/A" in the screenshot, that should instead read "1104".

Is the "INDEX(MATCH)" approach not appropriate here?

Let me know if I need to clarify more.

Upvotes: 1

Views: 65

Answers (2)

Vegard
Vegard

Reputation: 4917

Your formula is somewhat backwards.

Try this:
=INDEX($B$3:$B$13, MATCH(C9, $A$3:$A$13, 0))

The syntax is:
=INDEX(the array where my return value is located, MATCH(the reference value I want to find, the array where my reference value is located))

The essential error is that you had columns A and C in the wrong order inside your MATCH formula - you told Excel to look for values from col A somewhere within col C, which results in #N/A when the value in col A doesn't exist in col C (which is the case for 5min timestamp in A8). It's also the reason most of your actually filtered data (from row 8 and down) is wrong.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

This answer assumes that the flare data recorder date column is complete. One approach to your problem would be to use VLOOKUP on the flare date against the EPP date column adjacent to its data column, and rebuild that second data column the way you want.

Insert a new column in between the current B and C, such the new column becomes an empty column C. Then enter the following formula into C3:

=IFERROR(VLOOKUP(A3,D$3:E$100,2,FALSE),"no match")

The basic idea here is to lookup each complete date against the EPP data recorder data set, and then fetch the corresponding flow rate value from the (currently) column D. My formula would report no match should a date have no EPP flow data.

Upvotes: 1

Related Questions