Vlookup recognize only the first for digits

[timhue88]

Hi all,

I have a problem.


Currently, i have a sheet named "A" and it has accounts in 4 digits like A123, assuming in cell A1, with other data on the first row. there is a value on cell A3 of 1,000,000.

However, in my other sheet, i have data of account A123. But the account is not in the short form instead in A123USD123. I would like vlookup to only take the first 4 values A123 and recognize A123USD123 as a match.

i would like to have the 1,000,000 in my other sheet. how should my formula for vlookup be?

 

[Aladin Akyurek]

Try something like...

=VLOOKUP(F2&"*",Table,2,0)

where F2 houses a look up value like A123.

 

[timhue88]

thank you very much for the reply.

however, the formula doesnt work.

let me clarify my question...

in sheet A,

i have accounts

123USD123 1
234SGD234 2
433SEK231 3
499HKD123 4


in sheet B,

I have accounts

123 USD
499 HKD
433 SEK
234 SGD

what i require is for sheet b to get the values

123 USD 1
499 HKD 4
433 SEK 3
234 SGD 2


thanks so much for your help!

 

[Aladin Akyurek]

thank you very much for the reply.

however, the formula doesnt work.

let me clarify my question...

in sheet A,

i have accounts

123USD123 1
234SGD234 2
433SEK231 3
499HKD123 4


in sheet B,

I have accounts

123 USD
499 HKD
433 SEK
234 SGD

what i require is for sheet b to get the values

123 USD 1
499 HKD 4
433 SEK 3
234 SGD 2


thanks so much for your help!

Try...

Sheet_B

C2, just enter and copy down:

Either...

=INDEX(Sheet_A!$B$2:$B$5,MATCH(A2&B2&"*",Sheet_A!$A$2:$A$5,0))

Or...

=VLOOKUP(A2&B2&"*",Sheet_A!$A$2:$B$5,2,0)

Or, if there are multiple relevant records whose figures you want to totalize...

=SUMIF(Sheet_A!$A$2:$A$5,A2&B2&"*",Sheet_A!$B$2:$B$5)

 

[timhue88]

thanks aladin for your prompt response. you are a life-saver.

 

[Aladin Akyurek]

thanks aladin for your prompt response. you are a life-saver.

You are welcome. Thanks for providing feedback.