Forum Discussion
Search for a number in another sheet and replace with a different column name
I have multiple sheets in an excel file. Sheet1 has multiple columns, first column is barcode(for eg. 5463577263). I want to find this barcode in sheet2 and grab required fields using vlookup.
The problem here is my sheet2 has barcode plus some number at the end of it. For eg in this case 54635772632(11 digits instead 10). Because of this my vlookup is failing.
Is there is any way I can customize my vlookup to partially look for the value and do the rest of functionality of vlookup ? Really appreciate any help.
Sheet1
Barcode Name
5463577263 =vlookup(A2, Sheet2!A2:C100, 2, FALSE)
Sheet2
Barcode Days Name
54635772632 15 Transistor
3 Replies
- SergeiBaklanDiamond Contributor
Hi Srini,
For data like this
the formula could be
=INDEX($D$1:$D$5,MATCH(1,INDEX(--(VALUE(LEFT($C$1:$C$5,10))=$A$1),),0))
and attached
- Srini SanagapalliCopper Contributor
Sergei, Thank you very much for your quick response on this. You saved me lot of time to solve this.
- SergeiBaklanDiamond Contributor
Srini, you are welcome