String seach within an Array

[Jedi]

I am trying to use an array (2 columns) with a cell value
(Reference) used as the search criteria within the array...
So far so good...
BUT the search within the array is a part of the overal
string within the array...
I.E.
Search Reference cell value is '100'
The Array cell value is typically '50,60,100,200' or blank
Therefore I have to search within the cell string for the
match, then return the corresponding array column cell
value.

Any help would be greatly appreciated.

 

[Frank Kabel]

Hi
if i understood you correctly VLOOKUP is probably what
you're looking for. e.g.
=VLOOKUP(100,A1:B100,2,0)

 

[Jason Morin]

Try:

=INDEX(rng,MAX(ISNUMBER(SEARCH(D1,rng))*ROW(rng)),MAX
(ISNUMBER(SEARCH(D1,rng))*COLUMN(rng)))

where D1 is your search value and rng represents your
array.

This formula is an array, so after inserting it, press
ctrl/shift/enter. XL will place {} around the formula.

HTH
Jason
Atlanta, GA

 

[Jedi]

Thanks for the response,

But the VLOOKUP won't work since I have to search within
the cell value of the range (A1:B100)
E.G.
The string I am searching for is '100' whilst the cell
values within the range (A1:B100) would be
typically '20,30,50,100,200' (all one cell value).
So I would have to do an imbedded SEARCH within the
VLOOKUP, which I haven't been able to get a proper result
using that approach.

 

[Jedi]

Thanks for the quick response,

Tried this but didn't seem to work...

I'll try & explain the problem I have a little clearer...

I have two columns 'X' & 'Q' in a worksheet 'One'
I have two columns 'I' & 'G' in another worksheet 'Two'

Data is entered in cell 'I' = '100'
Cell in 'G' (same row) should display the cell value
(say 'Storm')from the cell in 'Q' where cell value 'I'
('100') is a sub-set of cell value 'X' ('20,30,50,100,300')

The problem I have is I can search an array for a cell
value to cell value match easy enough...
But the matching within the array of a sub-set of data
within a cell has me beaten...

I hope this helps in your diagnosis.

 

[Frank Kabel]

Hi
the you may try Jason' solution or as an altrnativethe
array formula
=INDEX(B1:B100,MATCH(TRUE,ISNUMBER(FIND(D1,A1:A100)),0))

where D1 stores your lookup value

 

[Aladin Akyurek]

Looks like you want:

G2 on sheet Two:

=INDEX(One!$Q$2:$Q$10,MATCH("*,"&I2&",*",","&One!$X$2:$X$10&",",0))

which must be confirmed with control+shift+enter instead of just with enter.

I2 houses a lookup value like 100.

 

[Jedi]

Thanks,

But the A1:A100 comes back with a #VALUE error.
The cell format was Text but even with the column changed
to Numeric, still comes back with the #VALUE.

 

[Frank Kabel]

Hi
have you entered the formula as array formula (with
CTRL+SHIFT+ENTER)

 

[Jedi]

Many many thanks,

The right answer comes out, still not sure exactly what
all of this formula does, but no doubt over the coming
days I'll work out exactly why it works...

Again thank you

 

[Jedi]

Yep you were right forgot to enter as an array,

Thank you very much for all your help

I have two solutions, (very similar) to the same problem,
now I'll spend some time & see how you did it

Again many thanks

 

[Frank Kabel]

Hi
use Aladin's solution as it is more robust. My formula
would also find a value like '10' if you have entered
something like '100,40'