VLookup using a cell number

%3CLINGO-SUB%20id%3D%22lingo-sub-3543236%22%20slang%3D%22en-US%22%3EVLookup%20using%20a%20cell%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3543236%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Spread%20sheet%20compare%20and%20my%20result%20have%20what%20cells%20were%20changed%20but%20I%20need%20to%20know%20what%20account%20number%20they%20go%20with%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%20(compare%20results%20shows%20cells%20changed)%3C%2FP%3E%3CP%3EA50%3C%2FP%3E%3CP%3EA222%3C%2FP%3E%3CP%3EB2%3C%2FP%3E%3CP%3EC45%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20second%20sheet%20has%20account%20numbers%20in%20column%20A1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20do%20a%20VLookup%20from%20sheet%201%20(compare%20results)%20to%20find%20out%20account%20number%20on%20sheet%202%20(column%20A).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20best%20way%20to%20do%20this.%26nbsp%3B%20I%20wish%20Spread%20Sheet%20compare%20would%20allow%20me%20to%20add%20fields%20before%20I%20exported%20results%20-%20that%20would%20solve%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3543236%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3543544%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20using%20a%20cell%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3543544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1432675%22%20target%3D%22_blank%22%3E%40jthorpe222%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(IF(ISNUMBER(NUMBERVALUE(MID(A1%2C2%2C1)))%2CNUMBERVALUE(MID(A1%2C2%2CLEN(A1)-1))%2CIF(ISNUMBER(NUMBERVALUE(MID(A1%2C3%2C1)))%2CNUMBERVALUE(MID(A1%2C3%2CLEN(A1)-1))%2CNUMBERVALUE(MID(A1%2C4%2CLEN(A1)-1))))%2CCHOOSE(%7B1%2C2%7D%2CROW(Tabelle2!%24A%241%3A%24A%2426)%2CTabelle2!%24A%241%3A%24A%2426)%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20the%20result%20in%20the%20attached%20file%20similar%20to%20what%20you%20want%20to%20do%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

1 Reply

@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?