Index/Match function Problem

[jackzha]

Hi everyone,

I ran into a pretty tricky situation here.

I use Index/Match function extensively to match up two spread sheets.

Our company has a coding system for materials to represent their different states.

For example, we have "ABC?", to show unsorted ABC. And we also have "ABC-", to show finished ABC.

I was running a Index/Match function to match up the inventory weight from two different registers, based on the inventoy codes described above.

Now, the problem is, when I need the "ABC?" weight number using the function, the Excel would return the weight value for "ABC-", because the "ABC-" comes first in the array before "ABC?", which is just right below "ABC-". But apparently the Excel thought it found what I needed and stopped searching in the array.

Could some one help me solve this issue?

Thanks a ton in advance.

Jack

 

[bs0d]

what about vlookup() ?

 

[CharlesChuckieCharles]

You are using a Wild Card character "?" anyway you could change it to let say a tilde ~

 

[CharlesChuckieCharles]

You are using a Wild Card character "?" anyway you could change it to let say a tilde ~

Not a tilde maybe a +

 

[jackzha]

Thanks for replying guys! The problem is, we have a ton of different materials, and a lot of them have this kind of coding convention. And the two reports are in flat table format, all material codes are basically in the same column. So the wild card character is not really helping me here. It is more of returning similar values with specific character in a text string undefined, if I recall correctly. However, I need Excel to precisely distinguish "ABC?" and "ABC-" as different values and return corresponding values.

 

[Gilliam]

I don't fully understand the problem you're faced with, but i can tell you that the MATCH function allows wildcards. The question mark indicated to excel that there is a wildcard character in place of the question mark. If you want to match "ABC?" you need =MATCH("ABC~?",A1:A2,0). The tilde lets Excel know that you're searching for the next character as it appears in the text string.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC*[/TD]
[TD]=MATCH("ABC?",A1:A5,0)[/TD]
[TD]Will Return 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC-[/TD]
[TD]=MATCH("ABC~?",A1:A5,0)[/TD]
[TD]Will Return 4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC^[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABC#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jackzha]

I don't fully understand the problem you're faced with, but i can tell you that the MATCH function allows wildcards. The question mark indicated to excel that there is a wildcard character in place of the question mark. If you want to match "ABC?" you need =MATCH("ABC~?",A1:A2,0). The tilde lets Excel know that you're searching for the next character as it appears in the text string.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ABC*[/TD]
[TD]=MATCH("ABC?",A1:A5,0)[/TD]
[TD]Will Return 1[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABC-[/TD]
[TD]=MATCH("ABC~?",A1:A5,0)[/TD]
[TD]Will Return 4[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]ABC^[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]ABC?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ABC#[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Yes, now I fully understands the impact of wild card character now. This formula works perfectly fine when I'm dealing with 1 or 2 materials. However, I need to do apply this formula to the whole array, which contains nearly 1,000 rows for about 500 different materials, all of which use the same coding convention.

Do you know if there is any other generic formula that I can apply to the whole array and not any particular material?

Thanks a ton for replying.

 

[CharlesChuckieCharles]

with the help of Gilliam

=MATCH(SUBSTITUTE(E3,"?","~?"),K2:K5,0)

E3 being the column containing your lookups and K2:K5 is the column containing your data

 

[jackzha]

with the help of Gilliam

=MATCH(SUBSTITUTE(E3,"?","~?"),K2:K5,0)

E3 being the column containing your lookups and K2:K5 is the column containing your data

This works perfectly and very elegant way to deal with our ancient inventor coding system! Thank you so much for your help!

My gratitude also goes to Gilliam and everybody who tried to help!

Cheers!