MID and FIND using Arrays

[seenfresh]

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

=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

 

[Norie]

And what object would that be?

And what array are you trying to refer to?

 

[seenfresh]

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]

Try this:

I just added a +1 after your find statement

=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]

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