String seach within an Array

  • Thread starter Thread starter Jedi
  • Start date Start date
J

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.
 
Hi
if i understood you correctly VLOOKUP is probably what
you're looking for. e.g.
=VLOOKUP(100,A1:B100,2,0)
 
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
 
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.
 
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.
 
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
 
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.
 
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.
 
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
 
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
 
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'
 
Back
Top