MID and FIND using Arrays

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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!

Thanks in advance
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
And what object would that be?

And what array are you trying to refer to?
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
reply

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

Does this answer your question...

[/quote]
 

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
Try this:

I just added a +1 after your find statement

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
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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!

Thanks for your advice everyone...
 

Forum statistics

Threads
1,181,421
Messages
5,929,805
Members
436,696
Latest member
Mr Rice

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top