For some reason VLOOKUP or some variance in that function is not working. I made a randomized list to emulate my situation. I have a large list and I want to check if that item is available in a shorter list. I have a situation I just want to check if it is available. Or in another situation, if availble, show the text from the same row of column x. This last one I did not include in this example because it also does not work. Would be appreciated if I could get help with that code as well.

All tables are in Sheet1 except the short table is in Sheet2.

Large table: (Table1, in sheet1 -> Yellow is what is in Table2)

| Name1 | Name2 | Info1 | Info2 |
|——-|——-|——-|——-|
| 20412 | 132 | Green | Apple |
| 75557 | 100 | Red | Water |
| 84242 | 229 | Pink | Apple |
| 35828 | 407 | Red | Egg |
| 49052 | 931 | Blue | Egg |
| 82247 | 657 | Pink | Water |
| 12840 | 291 | Blue | Water |
| 10117 | 649 | Pink | Egg |
| 71444 | 298 | Green | Water |
| 30180 | 912 | Pink | Water |
| 12189 | 270 | Green | Water |
| 15267 | 239 | Blue | Water |
| 25988 | 475 | Blue | Egg |
| 96528 | 120 | Blue | Apple |
| 41453 | 776 | Red | Beer |
| 63700 | 895 | Red | Water |
| 50723 | 366 | Red | Apple |
| 78113 | 681 | Blue | Beer |
| 28698 | 846 | Green | Water |
| 37998 | 484 | Green | Beer |
| 32781 | 337 | Green | Beer |
| 68340 | 593 | Blue | Water |
| 46335 | 921 | Red | Egg |
| 55532 | 389 | Pink | Water |
| 87567 | 723 | Green | Water |
| 15967 | 704 | Blue | Beer |
| 24392 | 766 | Green | Apple |
| 87966 | 431 | Blue | Apple |
| 48426 | 981 | Pink | Apple |

Short Table: (Table2, in sheet2)

| Name1_available | Name2_available |
|—————–|—————–|
| 75557 | 229 |
| 49052 | 407 |
| 71444 | 298 |
| 30180 | 270 |
| 15267 | 475 |
| 63700 | 895 |
| 15967 | 681 |
| 87966 | 593 |

When I use VLOOKUP it seems like the first row is working, the second is not. In my real files I often notice both are not working. Does this have to do with it is not able to look to the left or in previous sheets?
The formulas used: (in Sheet1)

First column: =VLOOKUP(Table1[@Name1];Table2;1;FALSE)
Second column: =VLOOKUP(Table1[@Name2];Table2;2;FALSE)

I (manually) marked the correct values green and the wrong red. The second column does have green but is of course not working.

And when I want to look it up and place a YES or NO is there is a match, is also not working as it should: (again manually marked green and red)

First column: =IF(ISNA(VLOOKUP(Table1[@Name1];Table2[@[Name1_available]];1;FALSE));"No";"Yes")
Second column: =IF(ISNA(VLOOKUP(Table1[@Name2];Table2[@[Name2_available]];1;FALSE));"No";"Yes")

Why is it not working as I think it should work. I think I read somewhere that it cannot look backwards (columns or sheets earlier) Is that true? Why? Is there an alternative that does work in the whole excel file?