Index Match Extracting Wrong Information

[hammingmi]

{=INDEX(Data!$A:$A,MATCH($A5&$B$3,Data!$D:$D&Data!$B:$B,0))}

I have my array to look for whatever is in B3 which will contain a number from 1-20. The problem is my data has 1-9 listed as 01-09 for this category so when it looks for "1" it is extracting from the Number 11 rather than 01. Is there any way I can adjust my formula to fix this problem or is this an issue with the data?

[JBeaucaire]

Always insert a spacer to keep the values from the two cells/ranges from comingling.

=INDEX(Data!$A$1:$A$1000, MATCH($A5 & "-" & $B$3, INDEX(Data!$D$1:$D$1000 & "-" & Data!$B$1:$B$1000, 0), 0))

[hammingmi]

Hey JBeaucaire,

When I inserted the spacers it did not return a value. Would adding the "-" not just make the array also search for text containing the characters within the hyphens?

Thanks

[JBeaucaire]

I'd have to see your formula and data to resolve the issue.

But look at the example formula given above. Notice the first "-" is between the two search values? This creates a single search value from the two with the spacer.

Then notice the second "-", that shows how we are Indexing two columns of data with the same spacer to CREATE the searchable concatenated columns within the formula itself. We do this all the time, a known method.