Vlookup not returning the right column

[Bandito1]

Hi all,

I use Vlookup to return data from different column.

I can't use xl2bb since my excel crashes then..
So i hope i can make it clear with pictures

On 1 sheet i have the lookup
In column B is a formula that looks if the number is in the list on the sheet 2016-2022.
=IF(A2="";"";IF(ISNA(VLOOKUP(SUBSTITUTE(A2;"G";"")+0;'2016-2022'!B:B;1;FALSE));"No";"Yes"))

That works
It shows Yes if it's in the list and No if it isn't

In the column next to it (column C) i want to return the value that is in column J of the sheet 2016-2022; "Cancelled"

I use the formula =IFERROR(VLOOKUP(A2;'2016-2022'!A:J;10;FALSE);"No comments") for it.

But it always returns "No comments"

I played with the column index number but it always shows "No comments"

Sorry for not using xl2bb again but the file crashes when i try to.

Someone knows what im doing wrong?

 

[I_know_nuffin]

TRY

IF(ISERROR(VLOOKUP(A2;'2016-2022'!A:J;10;FALSE),"No comments",VLOOKUP(A2;'2016-2022'!A:J;10;FALSE))

 

[Fluff]

How about

=IFERROR(VLOOKUP(A2;'2016-2022'!B:J;9;FALSE);"No comments")

 

[Bandito1]

I would be embarrassed if that was the solution.
Tried 8, 9, 10, 11 but all return "no comments" while it shouldn't

It looks like it gives error all the time since "no comments" should show when value_if_error?

 

[Fluff]

Make sure that A2 & column B are all numbers, or all text.

 

[Bandito1]

It works, thing i did wrong is that my range was still A:J instead of B:J
Don't know why this causes the error..
You can explain me that?

Also it displays "0" if there are no comments and not the words "no comments"

 

[Fluff]

Vlookup looks for the value in the 1st column of the lookup range.