Lookup

[Deelof]

How can I write a formula if a cell either starts with certain characters or the full cell contents are equal to another to return a value?

For example, cell content is TB00192-3 and either starts with TB or completely matches TB00192-3 returns the same value?

 

[RobP]

Hi,

maybe something like this can help you ?

Book1
	A	B	C	D	E	F	G
1
2		TB00192-3		TB00			TRUE
3
Sheet2

Cell Formulas
Range	Formula
G2	G2	=IFERROR(SEARCH(D2,B2)>0,FALSE)

 

[Deelof]

Hi,

maybe something like this can help you ?

Book1
	A	B	C	D	E	F	G
1
2		TB00192-3		TB00			TRUE
3
Sheet2

Cell Formulas
Range	Formula
G2	G2	=IFERROR(SEARCH(D2,B2)>0,FALSE)

Thanks, I need to look up to this list and return "X" if the cell value matches or starts with any of the below.
So, TB00192-3 would match as there are no others starting with TB.

BK

WM

DW

SA

KT

TS

STA

FF

FP

TV

CK

IB

IR

MW

VC

BU

GM

HD

UNGLUED

FD

TB00192-1

SB00135-1

SB00137-1

SD00068-1

SD00068-3

SB00200-2

TB00070-1

TB00194-3

TB00194-4

TB00192-3

TB00192-2

BE00067-1

TB00283-1

 

[Leo Skywalker]

hi there,

if your list starts in B5, you can try:

C2

=IF(SUM(IFERROR(MATCH($B$5:$B$37,LEFT(B2,LEN($B$5:$B$37)),0),))>0,"X","")

 

[Deelof]

hi there,

if your list starts in B5, you can try:

C2

=IF(SUM(IFERROR(MATCH($B$5:$B$37,LEFT(B2,LEN($B$5:$B$37)),0),))>0,"X","")

Thanks, that's worked but what if the cell contents are not on the list how can I expand the formula to say if B5 is not on the list then look to another tabs data and return the value if B5 is found? I've tried to add the below but errors:-

=IF(ISNA(VLOOKUP($B5,'extract 2.10.23'!$I:$L,4,FALSE)),"NOT ON WEB",VLOOKUP($B5,'extract 2.10.23'!$I:$L,4,FALSE)))

 

[RobP]

I would recommend you close this thread (mark above as a solution), and then open a new thread topic with your new requirements.

The benefit of that will be that many new people will see it, and you have more chance of a fix.

Hope thats okay.

cheers
Rob