Building a Formula Using Field Blank or Not Blank

meppwc

Well-known Member
Joined
May 16, 2003
Messages
626
Office Version
  1. 365
Platform
  1. Windows
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"
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
=IF(IFERROR(VLOOKUP(C2,D:D,1,0),0)=0,"Not in OBIEE",IF(VLOOKUP(C2,D:D,1,0)="","T&M",T2))
 
Upvote 0
try this:
Book1
ABCDEFGH
1Formula CvaluesGOBIEE Col DOBIEE Col POBIEE Col T
2XAANot BlankX
3T&MBBY
4ZCCSomethingZ
5 D
meppwc
Cell Formulas
RangeFormula
A2:A5A2=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,""))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

GuzmanServices

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

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))
 
Upvote 0
How about
Excel Formula:
=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))))
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top