Vlookup or If/Then to return a substitute cell for a cell that matches itself in a range

I know the title might seem confusing. This is what I'm trying to do.

I have a list of numbers in column A. Everything to the right of column A in the same row can be represented by the A cell. So A2:A40 are to be represented by A1, etc. I have another sheet where I have the list of all possible numbers. I want to run a formula that will return the value in the A1 cell for all numbers that have matches in A row. So in my other sheet, if A2 comes up, in the column next to it, I want A1 to appear. I'll attach a book to show what I mean. There are two tabs.

Thanks.

Shadefalcon,

Attached is a modified version of your posted workbook.
The items in sheet 'Table 1' are now all the results of formulas.

In cell B2:

Please Login or Register  to view this content.

In cell B3 and copied down to B16 (note that B16 is blank becuase the table's data has been exhausted):

Please Login or Register  to view this content.

In cell A2 and copied down:

Please Login or Register  to view this content.

It should work with both of those conditions. Just update the appropriate range references

Was your example file representative of your actual workbook? If so, you should be able to adapt the formulas. Otherwise, I'd have to see an example file that is actually representative of the workbook.

Way too late and not even a formula but hey! Here goes nuthin...

Please Login or Register  to view this content.

Shadefalcon,

Attached is a modified version of example book 2.

In sheet 'Table 1' cell B2 and copied down is this array formula:

Please Login or Register  to view this content.

Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how they get the curly braces {}. Do not try to add those yourself.

That's like a reversed VLOOKUP, how neat!