VLOOKUP - return value from alternate sheet on match and specific text when no match

[hbhowser]

Hello Everyone!

I've searched and tried a number of variations of functions but am not getting a consistent result.
I'm hoping that there is some excel magic out there and I'm thinking that it's not too difficult but I can't seem to resolve.

The below function searches for the value in A2 in sheet '2016-2017'!A:B and returns the value from B when the name matches.
=VLOOKUP(A2,'2016-2017'!A:B,2,TRUE)

My challenge is when there is no match on the name in A2, I would like to return "Need #" to the primary sheet.

I've tried IFERROR, MATCH, etc and am not getting the syntax correct.

Any/all suggestions would be much appreciated!
Thank you

 

[onlyadrafter]

Hello,

does this work as expected?

=IF(ISNA(VLOOKUP(A2,'2016-2017'!A:B,2,0)),"Need",VLOOKUP(A2,'2016-2017'!A:B,2,0))

 

[Nine Zero]

You need to to do FALSE for the last argument or else it will almost always return a solution, which is why your IFERROR wasnt running so it should be something like this

=IFERROR(VLOOKUP(A2,'2016-2017'!A:B,2,FALSE),"Need #")

 

[Fluff]

Hia & welcome to the board
how about this

=IF(ISERROR(VLOOKUP(A2,'2016-2017'!A:B,2,FALSE)),"need#",VLOOKUP(A2,'2016-2017'!A:B,2,FALSE))

Beaten to it

 

[Aladin Akyurek]

@hbhowser

Is '2016-2017'!A:B sorted in ascending order on column A? This question is triggered by the fact that you specify as match-type in your VLOOKUP...

 

[hbhowser]

@hbhowser

Is '2016-2017'!A:B sorted in ascending order on column A? This question is triggered by the fact that you specify as match-type in your VLOOKUP...

Thanks for the reply!
It is. I've made a bit of progress but the function is still flaky. I've removed the MATCH and am now using...

=IFERROR(VLOOKUP(A628,'2016-2017'!A:B,2,FALSE), "Need #")

I'm trying to check sheet 2016-2017 for the name in A628. If the complete name exists, return the value in B (which is a number). If the complete name does not exist, return the text "Need #". What I'm finding is that if the complete name doesn't exist - it finds the next best thing and returns that number. I'm think that I'm close but it's not working 100%.

Thanks again for the reply!

 

[hbhowser]

I apologize for being a newbie here. I see multiple options that I'm going to try now.
MUCH appreciated!

 

[NC Training Services]

Use the IFERROR not the IFISERROR - that is the long winded way - the IFERROR formula is quite new so a lot of people that haven't retrained use the old method - the ISERROR is much better and quicker. Also, if using TRUE yes the data does need to be sorted by the first column. if you use a FALSE then you do not. Good luck

 

[hbhowser]

Thank you, thank you!
I've replace my last function with =IF(ISERROR(VLOOKUP(A8,'2016-2017'!A:B,2,FALSE)),"Need#",VLOOKUP(A8,'2016-2017'!A:B,2,FALSE)) ... and it's working.
Again - much appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback