Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-07-2020, 02:30 AM
Danny cool Danny cool is offline Matching cells apparently don't match Windows 10 Matching cells apparently don't match Office 2013
Novice
Matching cells apparently don't match
 
Join Date: Jun 2020
Posts: 5
Danny cool is on a distinguished road
Default Matching cells apparently don't match

Hi,

So i have two cells that have B8 "22b" and the formula (INDEX('Data Entry'!$C$2:$BNW$2,(MATCH(F4,'Data Entry'!C1:BNW1)))) returns "22ba"

and the formula below.

IF("*" & $B8 & "*"=(INDEX('Data Entry'!$C$2:$BNW$2,(MATCH(F4,'Data Entry'!C1:BNW1)))),1,0)

The wild card has worked for me in the past but has stumped me this time.

The source and B8 both have a mixture of letters and numbers. the first two are always digits and the third a letter. The source might have two letters but the calculation max is one (like in the above example).
Attached Images
File Type: png 1.PNG (10.1 KB, 8 views)
File Type: png 2.PNG (10.5 KB, 10 views)
Reply With Quote
  #2  
Old 07-07-2020, 05:28 AM
Purfleet Purfleet is offline Matching cells apparently don't match Windows 10 Matching cells apparently don't match Office 2019
Advanced Beginner
 
Join Date: Jun 2020
Posts: 47
Purfleet is on a distinguished road
Default

Not sure why the wild card is not working without seeing the workbook, but it might be more efficient to use the left function around the index & match?

Just an idea
Reply With Quote
  #3  
Old 07-07-2020, 07:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Matching cells apparently don't match Windows 7 64bit Matching cells apparently don't match Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

1.The IF function does not support wildcards. instead you can use something like=IF(ISNUMBER(search("*"&your_string&"*",cell-nr)),...
2. Your INDEX formulas do not have the match_type (the default is 1 when omitted) so they will not return exact matches ( maybe that is what you want?)
Reply With Quote
Reply

Tags
not matching, return wrong result

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Read data on Sheet3 copy to Matching cells on Sheet1 Potholes Excel Programming 2 08-21-2019 11:53 PM
Matching cells apparently don't match Match Multi Cells gdavey Excel Programming 1 09-06-2015 11:09 AM
Matching Criteria against a range when criteria is not in contiguous cells?? GMorris Excel 9 08-20-2014 02:15 AM
Apparently empty (blank) cells aren't empty daymaker Excel 3 03-08-2012 03:41 PM
How to merge matching cells vertically? Odiseh Excel 1 01-02-2010 02:41 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft