Vlookup with IF and OR statements

[dbarbella]

Hey Gang,
Having a hard time working out the right syntax for this.

I have a working formula that checks if a cell (5th column) in another sheet is not blank.
If it's not blank, it places an asterisk in the cell holding the formula

=IF(VLOOKUP($B4,'Other Sheet Name'!$B$4:$S,5,False)<>"","*","")

I need to expand on this, and check if either of 2 cells is blank

So same formula, but....
Check if cell 5 OR 10 (in the other sheet) has a value
If either (doesn't have to be both) has a value then place an asterisk in the cell holding the formula

I'm just having a tough time getting the formula syntax correct for that.

Thanks so much for any help or advice.

-Dave

 

[jtakw]

Hi,

You're missing a cell reference above $B$4:$S?, I just used 5, since you're looking up 2 different columns, something like this:

=IF(OR(VLOOKUP($B4,'Other Sheet Name'!$B$4:$S5,5,FALSE)<>"",VLOOKUP($B4,'Other Sheet Name'!$B$4:$S$5,10,FALSE)<>""),"*","")

 

[dbarbella]

Thanks so much, I'll give it a go

 

[Aladin Akyurek]

Let's suppose that the other sheet is Sheet2.

If the VLOOKUP bit is expected to return text, we can have in Google Sheets...

=IF(isna(ArrayFormula(lookup(rept("z",255),vlookup($B4,Sheet2!$B$4:$S,{5,10},false)))),"","*")

If the VLOOKUP bit expected to return a numeric value, we can have in Google Sheets...

=IF(isna(ArrayFormula(lookup(9.99999999999999E+307,vlookup($B4,Sheet2!$B$4:$S,{5,10},false)))),"","*")

If both possible...

=iferror(if(or(vlookup($B4,Sheet2!$B$4:$S,5,False)<>"",vlookup($B4,Sheet2!$B$4:$S,10,False)<>""),"*",""),"")