VLOOKUP with IF or LOOKUP - Which is the right one to use?

[shauneyd]

Hi all,

New to the Forum and only my 2nd post so far.

I have a spreadsheet with multiple Worksheets and quite a lot of rows.

On one of the Worksheets I have the following 2 columns:

On the 2nd worksheet I have the following 2 columns:

What I am looking to do is on the first worksheet above is use a Formula to check if if the Department exists on the other Worksheet and if it does to return the value of 'Stage work is at'. Now, I am looking to return 'Yes' if it equals 'Complete' and 'No' if it is any other entry (including blanks).

I have been tinkering with VLOOKUP and LOOKUP but just can't seem to return the right result.

Is VLOOKUP right for this (possibly combined with an IF Statement) or should I be using LOOKUP?

Many thanks for any help or advice you can offer on this, I really appreciate it.

 

[6StringJazzer]

LOOKUP has its uses but is mostly provided for backwards compatibility for older versions of Excel before VLOOKUP (and HLOOKUP) was introduced. Most of the time VLOOKUP is the way to go. (VLOOKUP and HLOOKUP were themselves made obsolete by XLOOKUP in the latest versions.)

I would do this:

=IF(VLOOKUP(B2, Sheet2!$A:$B, 2, FALSE)="Complete","Yes","No")

There are probably other more "clever" ways to do this, but it's obvious what this formula is doing.

 

[shauneyd]

LOOKUP has its uses but is mostly provided for backwards compatibility for older versions of Excel before VLOOKUP (and HLOOKUP) was introduced. Most of the time VLOOKUP is the way to go. (VLOOKUP and HLOOKUP were themselves made obsolete by XLOOKUP in the latest versions.)

I would do this:

=IF(VLOOKUP(B2, Sheet2!$A:$B, 2, FALSE)="Complete","Yes","No")

There are probably other more "clever" ways to do this, but it's obvious what this formula is doing.

Thank you so much 6StringJazzer, I am 'almost' there with that one. It is working great but I am getting #N/A when the Department is not found on the other sheet.

I'll try and see if I can add something to your formula to prevent returning those values.

I really appreciate your help with this, thank you for taking the time.

 

[6StringJazzer]

This returns a blank if the department is not found. I'm pretty sure IFERROR is available in 2016 but if not let me know.

=IFERROR(IF(VLOOKUP(B2, Sheet2!$A:$B, 2, FALSE)="Complete","Yes","No"),"")

 

[shauneyd]

This returns a blank if the department is not found. I'm pretty sure IFERROR is available in 2016 but if not let me know.

=IFERROR(IF(VLOOKUP(B2, Sheet2!$A:$B, 2, FALSE)="Complete","Yes","No"),"")

Brilliant 6StringJazzer. This works absolutely perfect, thank you so much. I am very grateful to you.

 

[6StringJazzer]

You're welcome, glad to help!