## 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

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

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 […]

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

INDEX and MATCH – multiple criteria and multiple results

The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]

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

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

INDEX MATCH with multiple criteria

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form