Forum Discussion

ld's avatar
ld
Copper Contributor
May 02, 2023
Solved

VLOOKUP with IF and partial match

I'm working on automating some tracking spreadsheets that are all connected with Power Query. I have one query loaded to a sheet ("All Status" in formulas) that I am trying to reference on the main t...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    May 02, 2023

    ld 

    =IFNA(IF(OR(ISNUMBER(SEARCH("Under Review",VLOOKUP(B3,'All Status'!A:F,5,FALSE))),VLOOKUP(B3,'All Status'!A:F,5,FALSE)="Submitted"),"yes",""),"")

    =IFNA(IF(OR(ISNUMBER(SEARCH("Under Review",VLOOKUP(B3,'All Status'!A:F,5,FALSE))),VLOOKUP(B3,'All Status'!A:F,5,FALSE)="Submitted"),"yes",""),"")

     

    This formula returns the intended result in my sheet.
     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
May 02, 2023

ld 

=IFNA(IF(OR(ISNUMBER(SEARCH("Under Review",VLOOKUP(B3,'All Status'!A:F,5,FALSE))),VLOOKUP(B3,'All Status'!A:F,5,FALSE)="Submitted"),"yes",""),"")

=IFNA(IF(OR(ISNUMBER(SEARCH("Under Review",VLOOKUP(B3,'All Status'!A:F,5,FALSE))),VLOOKUP(B3,'All Status'!A:F,5,FALSE)="Submitted"),"yes",""),"")

 

This formula returns the intended result in my sheet.
 

  • ld's avatar
    ld
    Copper Contributor
    May 02, 2023
    This worked for me too. Thank you so much, I've been at this for hours so your help is greatly appreciated!!

Resources