## Match a range value containing both text and numerical characters

Formula in cell C11:

This formula is an array formula. To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell C11

The MID function returns a given number of characters from a value. This allows us to extract only the numbers from the value.

MID(C10,4,999) becomes MID("SEC48",4,999)

and returns "48".

This is still a text value so in order to use that, we must first convert it to a numerical value.

MID(C10,4,999)*1 becomes "48"*1 and returns 48.

The second argument in the LOOKUP function is the lookup list. This list must also be converted into numbers. I will use the same technique described in the previous step to extract and convert the numbers from cell range B3:B8.

MID(B3:B8,4,999)*1 becomes MID({"SEC1"; "SEC51"; "SEC102"; "SEC153"; "SEC204"; "SEC255"}, 4, 999)*1

becomes {"1";"51";"102";"153";"204";"255"}*1 and returns {1;51;102;153;204;255}

The LOOKUP function matches the lookup value to a list of range values and returns the corresponding value. Remember that the list of values must be sorted in an ascending order for it to work.

LOOKUP(MID(C10,4,999)*1,MID(B3:B8,4,999)*1,D3:D8)

becomes

LOOKUP(48,{1;51;102;153;204;255},{"Lot1"; "Lot2"; "Lot3"; "Lot4"; "Lot5"; "Lot6"})

and returns "Lot1" in cell C11.

### Download Excel *.xlsx file

Match text and numbers combined.xlsx

### Alternative formula

Identify the position of a value in an array.

Match a criterion and extract multiple corresponding table headers

Vikas asks: i need to extract the headers from a grid based on value in left most column example row [โฆ]

The array formula in cell D12 matches two values in two columns each and returns a value on the same [โฆ]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form