Menu
I have a table with some numbers stored as text (UPC codes, so I don't want to lose leading zeros). COUNTIF() recognizes matches just fine, but MATCH() doesn't work. Is there a reason why MATCH() can't handle numbers stored as text, or is this just a limitation I'll have to work around?
471
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.
When you see a #VALUE! error in your XLOOKUP function, the most likely reason is that your lookup array and your return array are not the same size. In my example, the reason the arrays are different sizes is that there is a blank cell at the end of my return array.
If set to TRUE or "1" (which is the default) VLOOKUP will allow a non-exact match. If set to "0" or FALSE, VLOOKUP will require an exact match. In this case, we definitely want to allow a non-exact match because the exact sales amounts will not appear in the lookup table, so I'll use TRUE.
Re: Index Match Match - wrong value returned Your problem was that your range of menu lookup and your definition to Table1 did not correspond. Once you have defined a table any names you need for validation or to identify parts of the table can be defined in terms of the structured references.
Functions like MATCH, VLOOKUP and HLOOKUP need to match data type (number or text) whereas COUNTIF/SUMIF make no distinction. Are you using MATCH to find the position or just to establish whether the value exists in your data?
If you have a numeric lookup value you can convert to text in the formula by using &"", e.g.
=MATCH(A1&"",B:B,0)
....or if it's a text lookup value which needs to match with numbers
=MATCH(A1+0,B:B,0)
168
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
