Omit numbers in vlookup search

[Cookie boy]

Hello

I'd like to vlookup from the attached sheet but the number i'm after is inside another number.

I've attached the sheet. Column B: has these numbers; 26800123330 I'd like to omit the first 5 and the last one and search on 012333.

Is that possible?

Many thanks

Rob

[HansV]

Do you want to look up 012333 in a table on another sheet, or do you want to search for 012333 in column B in this sheet?

[Cookie boy]

[quote name='HansV' post='768421' date='31-Mar-2009 09:30']Do you want to look up 012333 in a table on another sheet, or do you want to search for 012333 in column B in this sheet?[/quote]


thanks Hans

I'd like to lookup from another sheet

rob

[HansV]

Instead of VLOOKUP, you can use a combination of INDEX and MATCH in an array formula (i.e. confirm the formula with Ctrl+Shift+Enter instead of just Enter).
Let's say the value that you want to look up is in cell A2 on the active sheet. You want to match this value to the 6 characters starting at position 5 in column B of Test sheet. And you want to return the corresponding value from column G on Test sheet.

=INDEX('Test sheet'!$G$6:$G$417,MATCH(A2,MID('Test sheet'!$B$6:$B$417,5,6),0))

Don't forget to confirm with Ctrl+Shift+Enter!