Isnumber(search results misleading

[DHFE]

I've got a table 24x24 (excluding headers) the row and column headers will change depending on entries elsewhere in the workbook.
The column headers will either be single or double numbers - 1 / 2 / 3 / 4 / 5 or 1,2 / 3,4 / 5,6 / 7,8 / 9,10 or a combination - 1 / 2 / 3,4 / 5,6 etc
The row headers will be a list of the numbers - 1,2,3,4,5,6 / 3,4,9,10 / 1,2,5,6,9 etc.

My table is set out to show if the row header contains the column header and if so will show TRUE, if not FALSE.

The problem i'm having is when there's numbers over 10 or 20 etc the table will show true if the column headers have 1 or 2 as options.
Is there anyway for these to be discounted if the column header numbers aren't 1 or 2 and are 10s or 20s?

Sorry, i should say - numbers can appear on their own in both headings, when listed with another there will be a comma between them (no space)

[FlameRetired]

I would need to see a "hands-on" sample Excel file to better understand what you are working with. When you upload a file .... and please no pictures or screen shots ... many of our browsers won't read them ... please be sure to desensitize the data.

Include enough data (before section) to show us what you are starting with and an after section (manually typed if necessary) that shows expected outcome.

If you are not familiar with how to upload an Excel file on the Forum:

To attach a file to your post,
click “Go Advanced” (next to Post Quick Reply – bottom right),
scroll down until you see “Manage Attachments”,
click that and select “Add Files” (top right corner).
click on “Browse”
select your file(s)
click “Open” click “Upload” click “Done” (bottom right)
click “Submit Reply”
Once the upload is completed the file name will appear at the bottom of your reply.

[DHFE]

I'll do my best to get a desensitized version of the file uploaded, but basically all I'm looking for is a formula which can differentiate between a 1 and 11 to not give me a TRUE result against a 10,11,12,etc when looking for a 1.

As the table is 24x24 and the column header can contain double numbers, this means it can list up to 47,48 as an option. This would mean i would get a TRUE result when looking for a 4, 7 or 8. I don't want this - i need for it to look only for the exact numbers.

[samba_ravi]

pls attach a sample excel file

[Glenn Kennedy]

Is this just a case of using MATCH???

=MATCH(J1,A1-Z1,0)

where J1 contains 1 and A1-Z1 contains your headers.....

[DHFE]

Seriously...how impatient can you be - i said I'll do my best to get a file uploaded and you wait a whole 5 minutes before telling me again... I need time to make it - plus I'm at work so I can't just stop working.

isnumber.xlsx
Here's the file.

There's 3 tables.

Table A - The only data that's important is Column A. These cells will contain either single or double numbers as explained previously (up to a max 47,48)
Table B - The only data that's important is Column H (H-M merged cell). These cells will contain the listed numbers anywhere from a single number up to all possible 48.

Table C is where the formulas are. The column headers are Table A Column A, the row headers are Table B Column H.
I need the table contents to show TRUE if the column header exists in the row header. As you can see however B59:C31 are showing as TRUE because the numbers 1 and 2 appear in the row header, but it's not because 1 and 2 appear individually but rather as part of 10, 11 and 12. This is wrong.

@Glenn
I've tried using match, but that gave me false FALSE results

[ChemistB]

Try in B55 copied across and down

=IF(B$54="",FALSE,IF(LEFT($A55,LEN(B$54)+1)=B$54&",", TRUE, IF(ISNUMBER(SEARCH(","&B$54&",",SUBSTITUTE($A55," ",""))),TRUE,FALSE)))

This can be shortened to

=OR((LEFT($A55,LEN(B$54)+1)=B$54&","),(ISNUMBER(SEARCH(","&B$54&",",SUBSTITUTE($A55," ","")))))

Does that work for you?

[DHFE]

Nearly but both methods lose the last numbers;
in row 1 - 7,8 is displayed as FALSE when it should be TRUE
in row 2 & 3 - it's 5,6
row 4 has no TRUE results when it should have 7,8

etc

i'm guessing this is because of the formula checking to see if the value matches B$54 & ","

[DHFE]

So long as it works it doesn't really need to be eloquent, lol.

Again it's just slightly off - the rows which have more than one column being TRUE work, but rows 58 (7,8) and 62 (13,14) still show all as FALSE.

[ChemistB]

Maybe someone else can come up with a more eloquent formula but

=OR((RIGHT($A55, LEN(B$54)+1)=","&B$54),(LEFT($A55,LEN(B$54)+1)=B$54&","),(ISNUMBER(SEARCH(","&B$54&",",SUBSTITUTE($A55," ","")))))

[ChemistB]

Okay, how about this?

=IF(B$54<>"",OR(B$54=$A55,(RIGHT($A55, LEN(B$54)+1)=","&B$54),(LEFT($A55,LEN(B$54)+1)=B$54&","),(ISNUMBER(SEARCH(","&B$54&",",SUBSTITUTE($A55," ",""))))))

[DHFE]

@ChemistB
That seems to have done the trick, thanks for grinding that out it's been a major help

@AlKey
That works except if the header cells are blank, in which case it shows a false TRUE

[AlKey]

Hi DHFE,

Try this one

=IFERROR(IF(LOOKUP(10^308,--SEARCH(","&B$54&",",","&$A55&",")),TRUE),FALSE)

[ChemistB]

Very nice AlKey, using Alkey's logic, mine can be shortened to

=IF(B$54<>"",ISNUMBER(SEARCH(","&B$54&",",SUBSTITUTE(","&$A55&","," ",""))))