Building a Formula Using Field Blank or Not Blank

[meppwc]

Can someone please offer assistance?
I need a formula in cell C2 of worksheet "MAIN" that considers the following logic:
Using the value in G2 of "MAIN" worksheet, search column D of the "OBIEE TABLE" worksheet
If the value is found:
- Look at column P of "OBIEE TABLE", if the cell is blank, then, "T&M", if the value in column P is not blank, then, map the value in column T
If the value is not found, then "Not in OBIEE"

 

[GuzmanServices]

=IF(IFERROR(VLOOKUP(C2,D:D,1,0),0)=0,"Not in OBIEE",IF(VLOOKUP(C2,D:D,1,0)="","T&M",T2))

 

[GuzmanServices]

=IF(IFERROR(VLOOKUP(C2,D:D,1,0),0)=0,"Not in OBIEE",IF(VLOOKUP(C2,D:D,1,0)="","T&M",T2))

Sorry, please see my corrected formula.
=IF(IFERROR(VLOOKUP(G2,D:P,13,0),0)=0,"Not in OBIEE",IF(VLOOKUP(G2,D:P,13,0)="","T&M",T2))

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Formula C		valuesG			OBIEE Col D	OBIEE Col P	OBIEE Col T
2	X		A			A	Not Blank	X
3	T&M		B			B		Y
4	Z		C			C	Something	Z
5			D
meppwc

Cell Formulas
Range	Formula
A2:A5	A2	=LET(lkup,$C2,ObieeD,$F$2:$F$4,ObieeP,$G$2:$G$4,ObieeT,$H$2:$H$4,InObiee,IF(ISNUMBER(MATCH(lkup,ObieeD))=FALSE,"Not In Obiee",""),info,IF(XLOOKUP(lkup,ObieeD,ObieeP)="","T&M",XLOOKUP(lkup,ObieeD,ObieeT)),InObiee &IFERROR(info,""))

 

[meppwc]

hmmm........it should have returned "T&M", because the value searched on was found in the "OBIEE TABLE", but the value in P is blank.

 

[GuzmanServices]

hmmm........it should have returned "T&M", because the value searched on was found in the "OBIEE TABLE", but the value in P is blank.

If you're responding to my 2nd formula, please note I omitted the worksheet "OBIEE TABLE" on the VLOOKUP Range references to Columns D & P.

 

[meppwc]

GuzmanServices​

Yes, speaking to your formula. I added the worksheet name in the formula and it still returns "NOT IN OBIEE"

 

[GuzmanServices]

GuzmanServices​

Yes, speaking to your formula. I added the worksheet name in the formula and it still returns "NOT IN OBIEE"

In that case. please try the following revised formula.
=IF(IFERROR(VLOOKUP(G2,'OBIEE TABLE'!D:D,1,0),0)<>1,"Not in OBIEE",IF(VLOOKUP(G2,'OBIEE TABLE'!D:P,13,0)="","T&M",T2))

 

[Fluff]

How about

=LET(m,MATCH(G2,'OBIEE TABLE'!D2:D1000,0),IF(ISNA(m),"Not in OBIEE",IF(INDEX('OBIEE TABLE'!P2:P1000,m)="","T&M",INDEX('OBIEE TABLE'!T2:T1000,m))))

 

[meppwc]

Still having problems. I apologize and appreciate all efforts. Maybe I incorrectly explained what I need.
Allow me to attempt again:
SCENARIO 1
Search on the value in G2 of "MAIN" against "OBIEE TABLE" (column D)
The value from G2 was found, thus, we then go to the same row where the value was found (in my case that is D8638) "OBIEE TABLE" column P.
Cell P8638 is blank, thus, the value in G2 of "MAIN" ="T&M"
SCENARIO 2
Search on the value in G2 against "OBIEE TABLE" (column D)
The value from G2 was NOT found, thus, the value in G2 of "MAIN" = "NOT IN OBIEE"
SCENARIO 3
Search on the value in G2 against "OBIEE TABLE" (column D)
The value from G2 was found, thus, we then go to the same row where the value was found (in my case that is D8638) "OBIEE TABLE" column P
Cell P8638 is NOT blank, thus, we map the value in T8638 of "OBIEE TABLE" to G2 of "MAIN" (in this scenario G2 of "MAIN" = "8x5x4 GOLD"