• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Wildcards in INDEX MATCH formula

S

Sue

New Member
Hi


I'm trying to put together a formula which returns the value of a cell unless the cell contains a string of characters which will always be in the same format - X000.000. I'm finding the cell I require by using MATCH and getting the content of the cell using INDEX. I'm getting the correct values and can also exclude particular values, but if I use wildcards to exclude a type of value the formula treats it as text. I've tried "????.???" and "*.*". Should wildcards work in this situation? Any ideas what else I can try?
 
Wildcards should work. that being said, can we take a look at your formula and see what you are trying to find? Thx.
 
=IF(AND(INDEX($A:$A,MATCH(LEFT($G8,8),$A:$A,0)+8)<>"????.???",W8<>""),INDEX($A:$A,MATCH(LEFT($G8,8),$A:$A,0)+8),"")
 
I have a list of reference nos and under each reference no a list of dates applicable to that reference. The formula is taking the dates out and adding them to another list, ignoring any cells that contain the reference nos.
 
Hi Sue ,


I tried out your formula , and it would be nice if you could clarify certain parts of it.


1. I am assuming that G8 contains a value , say -9000.000 ; using the LEFT function on this number is not valid ; probably you can use the TEXT function on this to convert the number to a text string , and then use the LEFT function on this text string.


2. The INDEX function takes 3 parameters ; yours have only 2. Is the +8 intentional ? What will it do ?


Narayan
 
Hello, Sue!


I can tell you one problem---you've got your wildcards in a position that they won't be handled.


Also, just guessing, but if LEFT($G8,8) has to do with a desire to restrict your criteria to the beginning of the cell matched, no matter what or where you put LEFT(xxx,8), it won't do that. MATCH always matches the whole cell in a range or an array. To restrict your match to the first 8 characters, instead append an * to the end of the text you're looking for (the "what" in the syntax I describe below). And make sure to not include an * earlier in your "what" expression. To be complete in my comment---it would be possible to match against LEFT(xxx, 8), but only in a properly formed array formula, and then the only benefit would be that you could still use * in your "what" and be safe in the knowledge that every character you search for will still be in the first 8 characters. The price is that Excel will have to work much harder to complete your query.


///


Wildcards are processed by the MATCH() part of the expression. Match goes like this:

Code: 
MATCH(find what,where[, match type])


Wildcards can be present in the first parameter, "find what".  Use "match type" of 0 for an exact match, the only type that will fail if it doesn't find what you asked for instead of returning something else, and also the only type that allows wildcards.


The result of MATCH is the index (position) within "where" that the match was found.

[pre]INDEX(where,row,[column],[area]) returns the value of the matched cell when given the index positions.

So, an INDEX/MATCH vertical lookup goes:
[/pre]
=INDEX(where,MATCH(what,where,0))

And the result is the exact value of the cell that matched your "what".


////


If you search for "????.???", then the whole cell would have to be 8 characters long and contain a period in the 5th character.


If you search for "????.???*" then the cell would have to be 8 or more characters long, and have a period in the 5h character.


MATCH can't detect numbers versus text or anything, it just supports the ? (meaning exactly one unspecified character) and * (meaning 0 or more unspecified characters) as wildcards.


Asa
 
Column G contains another list of reference nos, but not the complete list that's in column A, and also with another ".000" on the end of each, hence comparing only the first 8 digits. The formula is looking for the reference no and then the cells beneath it, hence the +8 in this particular instance. Although the INDEX function has 3 parameters in the array form, only 2 are required. In this instance the column is irrelevant and thus not required. If I add in the actual reference no that this formula is excluding it works, but I cannot use the actual no in the formula as it is an unknown which could change.
 
Ok.


Can you write your requirement out, exactly, in words, naming cells and columns instead of what type of information they contain? That would make it easier to give you a formula that will do what you want.


when you say ".000" do you literally mean zeroes? or is that code for 3 numeric digits after a period?


The +8 explanation makes sense if you want the 8th cell down from the one that met the match.


Asa
 
Alternatively, maybe you can solve it yourself --


If your formula "practically works"...


You can't check that
Code: 
"value of matched cell" <> "?????.???" and expect wildcard processing.  the ?'s aren't in the first parameter to a MATCH function, they are part of a boolean true/false expression.


If you want to compare the result of an index/match to a wildcard expression, you can use SEARCH (find what,in what text,[start position if other than 1])
.


Hope that helps.

Asa
 
Hi Sue ,


Thanks for clarifying. Things are more clear but just !


When you are comparing numbers , any number of zeroes after the decimal point shouldn't really matter , unless there are more non-zero digits after the third decimal place. Is that so ? What I mean is , to compare -9000 with -9000.000 , you should always get a TRUE result ?


Suppose your formula is :


=IF(AND(INDEX($A:$A,MATCH($G8,$A:$A,0)+8)<>"????.???",W8<>""),INDEX($A:$A,MATCH($G8,$A:$A,0)+8),"")


Suppose W8 is not blank , G8 has -9000.0 , A20 has -9000 , A28 has 2000 in it ; the above formula returns 2000 , since that is the value 8 cells below the cell A20 which matches the value in G8.


If W8 is blank , then the above formula will return a blank.


But if no cell in column A matches the value in G8 , then the formula will return #N/A.


Is this what you intend to do ?


Narayan
 
Sue,

SEARCH, by the way, throws an #NA! error if it doesn't find what you search for.


To check that it DIDN'T find something, as you are trying to do, I think, check for #NA!.


e.g.


Code: 
=ISNA(SEARCH("????.???",INDEX(where,MATCH(what,where,0))))


gives you TRUE
if that matched cell DOES NOT meet the wildcard criteria (similar to <>), and FALSE if it does meet it.


Asa
 
No. The ".000" is part of a reference, it's not a number. In column A I have a list of reference nos in the format, say, "A143.001", which are like headings for dates beneath. In column G I have just a list of reference nos, the same type of reference as in column A, but with an extra ".000" on the end. What I'm aiming to do is put all the dates in column A into a row against the appropriate reference in column G. INDEX($A:$A,MATCH(LEFT($G8,8),$A:$A,0)+x)finds the date I'm looking for, where x starts at 1 to look at the row below the reference and increases by 1 in every column to look at each subsequent row. However, there comes a point when you reach the next reference no in column A, which I don't want to appear in the row, hence IF(AND(INDEX($A:$A,MATCH(LEFT($G8,8),$A:$A,0)+8)<>"????.???",W8<>""), which looks at 2 things: first it looks at the value found by the INDEX and MATCH; second it looks at the previous cell in the current row - as the next cell in column A would be another date it wouldn't be excluded, so once there's been a blank cell each of the following cells will be blank. I originally excluded the value in cloumn A saying IF(AND(INDEX($A:$A,MATCH(LEFT($G8,8),$A:$A,0)+8)<>LEFT($G9,8),W8<>""), which worked until there was a reference in column A but not in column G and then, of course, the value didn't equal LEFT($G9,8) and the formula didn't do what I wanted.


If I substitute the actual reference in the next row, A144.003, where I have ????.??? then it works, it just doesn't seem to like the wildcards.
 
Did you try my suggestion of using SEARCH to handle wildcards? the wildcard capability of MATCH isn't really relevant to your formula.
 
Yay :)


If you find that the wildcard match is not sufficient (it really is just confirming the placement of a decimal point), you could resort to use MID() to specifically check that certain parts of the text meet certain criteria.
 
You must log in or register to reply here.
Back
Top