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:

='Table 2'!A2

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

=IF(ROWS(B$1:B2)>COUNTA('Table 2'!$A$2:$E$5),"",IF(ROWS(B$1:B2)>SUMPRODUCT(SUBTOTAL(3,OFFSET('Table 2'!$2:$2,,,MATCH(B2,'Table 2'!$A:$A,0)-1))),INDEX('Table 2'!$A$2:$A$5,MATCH(0,INDEX(COUNTIF(B$1:B2,'Table 2'!$A$2:$A$5),),0)),B2))

In cell A2 and copied down:

=IF(B2="","",INDEX('Table 2'!$A$2:$E$5,MATCH(B2,'Table 2'!$A$2:$A$5,0),COUNTIF(B$2:B2,B2)))

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...

Option Explicit
Sub SplitData()
Dim i As Integer, j As Integer, k As Integer
Dim AC As Range, BC As Range
i = 0
k = 0

Set AC = ActiveSheet.Range("A2")
Set BC = Sheets("ResultTable").Range("A2")
While AC.Offset(i, 1).Value <> ""
    j = 0
    While AC.Offset(i, j).Value <> ""
        BC.Offset(k, 0).Value = AC.Offset(i, j).Value
        BC.Offset(k, 1).Value = AC.Offset(i, 0).Value
        j = j + 1
        k = k + 1
    Wend
    i = i + 1
Wend
End Sub

Shadefalcon,

Attached is a modified version of example book 2.

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

=IF(COUNTIF('Table 2'!$A$2:$E$15,A2)=0,"",INDEX('Table 2'!A:A,MIN(IF('Table 2'!$A$2:$E$15=A2,ROW('Table 2'!$B$2:$E$15)))))

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!