VLookup using a cell number

[jthorpe222]

Hello - 

I am using Spread sheet compare and my result have what cells were changed but I need to know what account number they go with:

Sheet 1 (compare results shows cells changed)

A50

A222

B2

C45

My second sheet has account numbers in column A1.

I need to do a VLookup from sheet 1 (compare results) to find out account number on sheet 2 (column A). 

What is the best way to do this.  I wish Spread Sheet compare would allow me to add fields before I exported results - that would solve this issue.

Thank you in advance.

[#2]

@jthorpe222 

=VLOOKUP(IF(ISNUMBER(NUMBERVALUE(MID(A1,2,1))),NUMBERVALUE(MID(A1,2,LEN(A1)-1)),IF(ISNUMBER(NUMBERVALUE(MID(A1,3,1))),NUMBERVALUE(MID(A1,3,LEN(A1)-1)),NUMBERVALUE(MID(A1,4,LEN(A1)-1)))),CHOOSE({1,2},ROW(Tabelle2!$A$1:$A$26),Tabelle2!$A$1:$A$26),2,FALSE)

Is the result in the attached file similar to what you want to do?