Incorporate Formula within VLOOKUP function

[BillTony]

Hi there,

I need to be able to incorporate / integrate a formula into a VLOOKUP thatbrings data from one worksheet over into another.

If there is an "L" (late) or "O" (overdue) within the cellI need to bring over "ERROR" - which is essentially a delinquentpayment. If neither are present - simply display whatever valuethat is actually present.

I've included the relevant code snippets below

Thanks in advance!

'VLOOKUP

=VLOOKUP(A2,'FilteredData'!AN:BA,14,FALSE)

'Evaluating":
=if(COUNTIF(A2,"=*L*")+COUNTIF(A2,"=*O*")>0,"ERROR","")

 

[Fluff]

Just replace the "" at the end of the 2nd formula with the Vlookup.

 

[BillTony]

The VBA-based code below is something I use with great frequency.

At the end of the initial VLOOKUP I typically return a blank if the lookup cell is blank , if not then return whatever is in the cell.

However, that spot (If...VLOOKUP="""","""",VLOOKUP) is where I wish to incorporate the COUNTIF's referenced above.

I just not sure how to go about that...

'Inserta VLOOKUP in Cell B3.

'ThisVLOOKUP is for AN(40): DK(115) / 76 (AN = FULL_ACCT_NO - DK = PREVIOUS_USER) -Filtered Data WORKSHEET.

    Range("B3").Formula= "=IF(VLOOKUP(RC[-1],'FilteredData'!C[38]:C[113],76,FALSE)="""","""",VLOOKUP(RC[-1],'FilteredData'!C[38]:C[113],76,FALSE))"

'CONDITIONALAutoFill.
    If Last_Row_ColA > 3 Then
        'Selection.AutoFillDestination:=Range("B3:B" & Last_Row_ColA)
       Range("B3").AutoFill Destination:=Range("B3:B" &Last_Row_ColA)
    End If

 

[Fluff]

Using you original formulae
=if(COUNTIF(A2,"=*L*")+COUNTIF(A2,"=*O*")>0,"ERROR",VLOOKUP(A2,'FilteredData'!AN:BA,14,FALSE))

 

[BillTony]

But isn't the first / initial VLOOKUP where I POINT to the relevant worksheet?

For example, I have my lookup value in cell A2 of worksheet "A".

I am placing a formula in cell B2 of worksheet "A".

I am EVALUATING the content found in worksheet "B" - which is named "FilteredData" in this particular example.

The results of this evaluation are then "brought back" to cell B2 - via the results of the VLOOKUP - and displayed either as an error based on the COUNTIF, or as a simple value if the condition "passes."

I hope that makes things a little more clear - at this point I'm beginning to confuse myself...

 

[Fluff]

Are your checking A2 on worksheet A for a value of L or O?
If so the formula from post#4 should work.

 

[BillTony]

Sorry to be such a pain...

I am using an "ID" (or, primary identifier) found in cell A2 of worksheet A to "initialize" my VLOOKUP search on worksheet B.

I need to "tie" my results to that ID.

If that ID is found on worksheet B, I then - and, only then - begin my evaluative process.

I then (attempt to...) check if that value in the corresponding cell of worksheet B meets the CRITERIA of the COUNTIF statement.

If it DOES, this would essentially result in an error being returned.

If it DOES NOT, I wish to return whatever value is found in that cell of worksheet B.

So - I have a "home-base" in worksheet A (cell A2 in this scenario) - which will, in all likelihood, contain a "like value" that exists on worksheet B.

But, the "failsafe" in this scenario is that the VLOOKUP will ultimately determine if that ID in worksheet A does indeed exist in worksheet B.

I need to retrieve a value from worksheet B that either meets the "Fail" criteria (and states that fact),or, does not meet the "Fail" criteria - in which case it returns whatever value may be present in the corresponding cell of worksheet B.

If I'm seemingly running around in circles on this whole premise - PLEASE tell me so!

 

[Fluff]

This has now gone beyond my (meagre) knowledge of formulae & won't be able to help anymore.
But it would help other, potential helpers, if you could clearly state what cells are what sheets, and supply some sample data from both sheets, including what you want the result to be.

 

[BillTony]

Well, they make this next to impossible to attach a file...

So, as a last resort - this is the best illustration of my data that I am able to supply.

Cheers!

Reference ID:	Value from WORKSHEET B:	COUNTIF / DATEVALUE:			VLOOKUP in Cell B2 - filled down.
101	319,663.09	ERROR			=VLOOKUP(A2,'WORKSHEET B'!A:B,2,FALSE)
102	179,758.52	ERROR
103	174601.00				Adjusted VLOOKUP in cell B2 - prevent "0" from being returned if source cell is blank
104					=IF(VLOOKUP(A2,'WORKSHEET B'!A:B,2,FALSE)="","",VLOOKUP(A2,'WORKSHEET B'!A:B,2,FALSE))
105	105,527.10	ERROR
106	07-11-2013	ERROR
107	47850.60				CountIf / DateValue in cell C2 - evaluates the data in column B of WORKSHEET B.
108	323,000.00	ERROR			If the data contains a comma (,) or is a Date - an "Error" is returned to column C of WORKSHEET A.
109					=IF(COUNTIF('WORKSHEET B'!B2,"=*,*")>0,"ERROR",IF(ISERROR(DATEVALUE('WORKSHEET B'!B2)),"","ERROR"))
110	06-25-2013	ERROR
					This is a simple example - the datasets and the results derived tend to become QUITE LARGE.
					For that reason I am trying to eliminate any "helper" columns in an effort to present a more "compact" reporting structure.
					Toward that end - I'm trying to "sandwich" the COUNTIF / DATEVALUE statements WITHIN the VLOOKUP. This would allow me - in this example - to eliminate column C of THIS worksheet. The lookup AND the result of the CountIf / DateValue would then be found combined in column B. Applied in the context of an actual project, this might allow the elimination of 25 to 50 columns overall. I have had no luck with my efforts thus far, but it seems "logical" that somehow these statements could be incorporated - somehow where the 1st iteration of the VLOOKUP ends? It is already returning a "conditional" result at that point - even though it's as simple as preventing a "0" being returned instead of a truly blank value.

<colgroup><col span="3"><col span="2"><col></colgroup><tbody>
</tbody>