Evaluate Date in Text string with vlookup help!

[Drivium]

I have a situation where I may have more than 1 job for a well. Therefore the well shows up twice in my data, which is ok. But in some cases the cell is a text string with a date at the end such as "Pending as of 7/8/2011". I need a vlookup formula to evaluate the date portion, figure out if it's bigger than the other dates for that time period and use it. For example, on the MAIN tab, in cell L20, I need a vlookup formula to choose between two dates (which may or may not be in text strings)

The Well is LCP017
Record 1 (on data tab)
Prior_Year Q1 Q2 Q3 Q4
10/12/2009 2/1/2011 Pending as of 4/8/2011 5/6/2011 5/6/2011
Record 2 (on data tab)
Prior_Year Q1 Q2 Q3 Q4
10/12/2009 Pending as of 1/23/2011 5/6/2011 5/6/2011 5/6/2011

The result should be:
Prior_Year Q1 Q2 Q3 Q4
10/12/2009 2/1/2011 5/6/2011 5/6/2011 5/6/2011

But, if the PENDING date happened to be higher (which in this case it's not), I still need it to show the "Pending as of" part of the string in the cell. Well LYD247 is an example where this would apply.

 

[Drivium]

For now, I'm testing the formula in cell L20 and L36 on the main tab (Q2 for wells LCP017 and LYD247)and cells O20 and O36 (Q3 for wells LCP017 and LYD247).

For LCP017 (just a change of the index_num 7 or 8)
Main tab Cell L20
Q2:
VLOOKUP($B20,ESP_Master_Status,7,FALSE)
Main tab Cell O20
Q3:
VLOOKUP($B20,ESP_Master_Status,8,FALSE)

For LYD247 (just a change of the index_num 7 or 8)
Main tab Cell L36
Q2:
VLOOKUP($B36,ESP_Master_Status,7,FALSE)
Main tab Cell O36
Q3:
VLOOKUP($B36,ESP_Master_Status,8,FALSE)

 

[Drivium]

When I get the #VALUE result with:

=IF(IF(ISNUMBER(VLOOKUP($B36,ESP_Master_Status,9,FALSE)),VLOOKUP($B36,ESP_Master_Status,9,FALSE),DATEVALUE(RIGHT(VLOOKUP($B36,ESP_Master_Status,9,FALSE),LEN(VLOOKUP($B36,ESP_Master_Status,9,FALSE))-14)))>IF(ISNUMBER(VLOOKUP($B36,ESP_Master_Status,9,TRUE)),VLOOKUP($B36,ESP_Master_Status,9,TRUE),DATEVALUE(RIGHT(VLOOKUP($B36,ESP_Master_Status,9,TRUE),LEN(VLOOKUP($B36,ESP_Master_Status,9,TRUE))-14))),VLOOKUP($B36,ESP_Master_Status,9,FALSE),VLOOKUP($B36,ESP_Master_Status,9,TRUE))

And click on the error icon, I can choose to evaluate and it shows where and what the error is, just not sure how to fix it.

 

[Drivium]

EDIT - you have to click SHOW CALCULATION STEPS

 

[Drivium]

I'm making an assumption that I can use VLOOKUP in this way. I'm assuming that FALSE means the first instance that matches the well, and TRUE means any other instance that matches the well. I wonder if there is a way to take the max of the vlookup findings? I'm sure the problem isn't your formula, because it works flawlessly with a regular cell reference. Still trying different variations with no luck....

 

[SkipVought]

I do not believe that VLOOKUOP will work, because it can only return a value from ONE ROW, and you need values from TWO ROWS.

You might want to look at the OFFSET function, which can return a RANGE. Use MATCH to find the correct row, based on WELL. Use COUNTIF to count the number of occurrences of WELL, that gives you the number of rows. Then using the INDEX and MATCH you can pick off the values in both rows.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Drivium]

Any chance you can throw out an instance of what you are describing... I still can only get the first instance. I don't get how to reference more than 1 row. Like for LCP017 for example.

 

[SkipVought]

Here is a formula for the RANGE, assuming that the lookup TABLE starts in A1
[tt]
=offset($A$1,match("LCP017",$A:$A,0),[highlight]colOffset[/highlight],countif($A:$A,"LCP017"),1)
[/tt]
where the value you want to return is [highlight]colOffset[/highlight] columns over from column A.

Then you put that formula into the INDEX() function in order to get specific values from the returned rows, in your case either the first or second if there is a second...
[tt]
=index([highlight]theFormula[/highlight],1,1)
=index([highlight]theFormula[/highlight],count([highlight]theFormula[/highlight]),1)

[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Drivium]

Which index?

=index(theFormula,1,1)
or
=index(theFormula,count(theFormula),1)

 

[Drivium]

I think for this instance, I am in need of a specific formula for my spreadsheet. Ive been reading non stop on this since my original post, but can't seem to translate any formula to work with my spreadsheet. In this version, I've cleaned up a lot of the data improved the function in general. For example, for LCP017, the cell on the MAIN tab K21, should have the value "5/6/2011" because it's greater than the alternate value for the same well, for the same quarter, on the DATA tab ("Pending as of 4/8/2011").

http://www.mediafire.com/?jq66auiawfws6gt