VLOOKUP or not VLOOKUP?

[lamaar]

I would like to know if there is any formula that can be used in place of VLOOKUP or used in conjunction with VLOOKUP?
I have a list of 250 model codes, each model code contains a number with an upper case letter or a lower case letter that is always the last character. For example, a model code of upper case 26E would not be the same as 26e. I needs to know if there is some formula that can be used to do this or would like to know where he could look for this, on perhaps a web site. I do not want to use a macro. Lamaar75@hotmail.com

 

[DarkSun]

This hopefully should work:

=IF(EXACT(C1,INDEX(E:E,MATCH(C1,E:E,0))),INDEX(F:F,MATCH(C1,E:E,0)),INDEX(F:F,MATCH(C1,E:E,1)))

C1 is value to look for.
Column E is where to look.
Column F is what to return.

this works for data as shown below:

25a test1
25b test2
25B test3
26D test4
26d test5

but will not work for data like:

FeG test1
Feg test2
fEg test3

I believe it will only work where there is one variation.

Give it a try.

 

[SimonDavis]

There is a convoluted way to do this, but I don't think it will work unless your model numbers are at least consistent.

Using you numbers, I would first extract the letter, which assuming the model number is in column A, I would put the followng in B:

=RIGHT(LEFT(a1,3),1)

This would take the E or e from the model.

You can then use the 'CODE' function to evaluate this letter. It is case sensitive so you will have data to distinguish each model.

This obviously won't work if the model numbers have letter/numbers in different relative positions.

I think that's what you were looking for - you'll need to do some further brainstorming to use this data, but I think from your post you can work it from there. If not post back.