# MID and FIND using Arrays

#### seenfresh

##### Well-known Member
I am trying to establish the best way to refer to a piece of an array...

For example in cell D2 the value = 4000-KTVQ-0 License Fee - KTVQ

In cell C1 = KTVQ

I tried using an Index and Match Function...

Code:
``=INDEX(PLATFORM.xls!\$E\$1:\$E\$150,MATCH(LEFT(C1,4),MID(PLATFORM.xls!\$D\$1:\$D\$150,FIND("-",PLATFORM.xls!D1:D150,1),4),0))``

This however does not work due to my invalid use of an array...

Can anyone suggest a method that will obtain my object...?

Any advice would be greatly appreciated!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
And what object would that be?

And what array are you trying to refer to?

Norie,

The criteria is in Column C ex.. KTVQ

and the match case array in Column D...
MID(PLATFORM.xls!\$D\$1:\$D\$150,FIND("-",PLATFORM.xls!D1:D150,1),4)
has above

and the return array is in column E and this column holds Numeric Values...

[/quote]

Try this:

Code:
``=INDEX(PLATFORM.xls!\$E\$1:\$E\$150,MATCH(LEFT(C1,4),MID(PLATFORM.xls!\$D\$1:\$D\$150,FIND("-",PLATFORM.xls!D1:D150,1)+1,4),0))``

Confirm w/ ctrl + shift + enter

HTH

I'll give this a try:

I ended up using a Vlookup with wildcards instead... much easier; at the time I didn't think of using it!

Replies
1
Views
129
Replies
3
Views
325
Replies
3
Views
184
Replies
2
Views
283
Replies
7
Views
787

1,219,581
Messages
6,149,119
Members
450,861
Latest member
metcala

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back