Excel 2007 : Vlookup for numbers and alphabet in the same list

[shehla.uhp1]

1223336545445

[Fotis1991]

Hi and welcome to the forum.

One way could be to use helpers and hidden columns for separating the code(Numbers & text)

Try to upload a small sample workbook..

[shehla.uhp1]

abababbabababbababababba

[npamcpp]

The formula you have posted looks up a country code and it works fine. AT and CZ are not present in $A$74:$A$107, so the formula returns #N/A, which means the lookup value cannot be found.

It seems that your description is about something different than the formula you posted.

[shehla.uhp1]

dont want to show this.

[shehla.uhp1]

dont want to show this.

[npamcpp]

Vlookup will return the first match that it finds. If you have more than one CH code in your list, only the first one will be returned.

[npamcpp]

you are only looking up the country code, nothing else.

[npamcpp]

I need formula that use product code in such a way that the code starting with number like "1406-ST/CF" have account code ending with -016 as this is in the sheet. and the product code starting with alphabet like "Custom 3207" must have account number ending with -008.

Can you explain the logic behind that?

In your data there is no visible connection between "1406-ST/CF" and -016 or "Custom 3207" and -008

I see that you have two blocks of data in your lookup table, but as I said above, the lookup criteria is only the country code. So you need to create a relationship between the country code and which block of data to use as a lookup table.

[shehla.uhp1]

dont want to show this.