COMPLICATED LOOKUP

  • Thread starter Thread starter lehigh
  • Start date Start date
L

lehigh

Hi All

This is the 3rd time That I ask this question.
I didn't formulate the question very well the last two times.

Here it goes!

$A$4 contains a string of letter codes as follows.

A, B, H, S1, T, T1, T2, V, W, Z (all of these codes are in one cell)

C4 may contain C, D
C5 may contain A, D, S1
C6 may contain H
C7 may contain P, X
C8 may contain T1

I would like B4 thru B20 to try to match any code in A4 with the codes
in C4 Thru C20 with a text "YES","NO" as follows:

B4 No
B5 Yes
B6 Yes
B7 No
B8 Yes

The formula would have to match individual codes within a cell against
individual codes from another cell. This is more than a stright LOOKUP








I hope this is clear enough

Thanks for your help

Tom Snyder
 
Need clarification.
A4 will contain for example ABH or S1W or VT2 .... correct
C4 will contain either C or D .... correct?
Bernard
 
I do not believe you understand the VLOOKUP function. Perhaps you could use
the FIND function instead.
 
Here's a go

=IF(OR(ISNUMBER(FIND(MID(C4,ROW(INDIRECT("1:10")),1),$A$4))),"YES","NO")

copy down

however it will fail if the C column can hold a single "S" for instance
(meaning an S w/o the company of 1), in that case the result is "YES"
although there is no single "S". I'd suggest that you use only one code in
C instead of multiple codes. If there cannot be "S" as opposed to "S1" in
the C column then the above will work
 
A4 could contain as many as 20 or 30 codes, all seperated by a comma
and a space. Some of these codes could have a 1 or a 2 or a 3 with
them.

The cells in column C will mostley contain one code letter, but some
of them can contain 2 or 3 code letters. Again, if there is more than
one code per cell, these would be seperated by a comma and a space.
 
Hi,

I wrote following function. May be that will help you.

Regards.


Haldun Alay

Option Explicit

Function FindMultipleValues(SourceText As String, LookupText As String) As
String
Dim SourceArray
Dim LookupArray
Dim SourceTop As Integer
Dim LookUpTop As Integer
Dim T As Integer
Dim i As Integer
SourceArray = Split(SourceText, ", ")
SourceTop = UBound(SourceArray)
LookupArray = Split(LookupText, ", ")
LookUpTop = UBound(LookupArray)
FindMultipleValues = "No"
For T = 0 To LookUpTop
For i = 0 To SourceTop
If LookupArray(T) = SourceArray(i) Then
FindMultipleValues = "Yes"
Exit Function ' we found one and it does not neccessary to
check other values
End If
Next i
Next T
End Function
 
=OR(ISNUMBER(MATCH(EVAL(CHAR(123)&CHAR(34)&SUBSTITUTE($A$4,",
",CHAR(34)&","&CHAR(34))&CHAR(34)&CHAR(125)),EVAL(CHAR(123)&CHAR(34)&SUBSTIT
UTE(C4,", ",CHAR(34)&","&CHAR(34))&CHAR(34)&CHAR(125)),0)))

which you need to confirm with control+shift+enter, not just enter.

Moreover, it requires EVAL which is part of the morefunc.xll add-in
(downloadable from: http://longre.free.fr/english/index.html).
 
Back
Top