CODEWITHSUNDEEP
excelexcel-formula
Josh Gust
Josh Gust

Reputation: 4445

Excel not pulling correct value format from table for use in Match() formula

I have a table of data imported through Power Query from a CSV. I'm trying to lookup a separate identifier from a different table based on a column value from the import.

When I write my Index( ,Match(), ) formula with reference to the cells from the imported table as the match source, I get #N/A. The evaluation steps show me that the cell value from the import table is a string literal (ie. "76"). The column and the table cells explicitly are "General" format as they are mixed numeric and alphanumeric (ie. 1, 2, 3, 3A, 3B, etc.).

I've painted format between the import and lookup tables to make sure they're the same.

In my troubleshooting, I've also added 76 to a cell outside the imported table and directed my index-match formula to that cell as the match source (also format painted same as the tables). When using the cell outside the table, the reference pulls the value as 76 and finds a value in the lookup.

So, what about having my cell reference in the import table is making the reference value resolve to a string literal and causing my match() to fail?

PS. I'm using Index-Match, but the same issue seems to be happening if I orient my lookup table to an order suitable to VLookup.

Upvotes: 0

Views: 403

Answers (1)

andrewb
andrewb

Reputation: 1146

I'm not sure if this helps, but to force MATCH to match numbers with strings, you can try appending double quotes at the end of both the lookup_value and lookup_array like so:

=MATCH(D1&"",A1:A5&"",0)

Confirmed with ctrl+shift+enter, because appending the "" to the array makes this an array formula.

enter image description here

As you can see, the "2" in the A column is a string (="2") but the 2 in the D column is a number. By appending "" both to the lookup value and array, excel will convert both of these into strings and match the 2 correctly.

Upvotes: 1

Related Questions