vlookup vs Index match function

The formula you are showing does not match your description. Your formula will return a value from column C of Sheet1. If you want an answer from column B then you want

Formula:

=VLOOKUP(A2,'Sheet 1'!A:B,2,FALSE)

Hi and welcome to the forum

Given the same data and conditions, both vlookup and index/match will return the identical results, and the only reason vlookup will "sometimes returns the wrong NUMBER code and has a conniption and randomises the order" is if your data does not match. Both will return the very 1st match they find, and then stop looking after that.

The main difference between the 2 search functions is that vlookup cannot look up/find anything to the left of the search column, whereas index/match does not have that restriction.

For instance, if you have data in column B that you want to search for, and then return the corresponding value from column A, vlookup will give an error, but index/match wont have that same problem.

If your vlookup is not performing as expected, check your data. Look for leading/training spaces. Test "identical" data using tests like =exact(cell-ref1, cell-ref2) etc. If you still have a problem, I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

INDEX & MATCH works in this way

=INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0))

As i can see gives same results as your VLOOKUP.

Can you tell us 1-2 wrong results that these formulas give to you?