Can VLOOKUP be fine-tuned?

[Csibész]

Hi,
Given a large number of books to label and sort in a small library. Authors and titles are in an Excel2010 workbook under win7 OS. Strings are grouped in alphabetical categories (Ygroup table). Using VLOOKUP function, the authors are assigned id#s as per the Ygroup table categories.

For some reason some of the authors would not fall where they should (id# marked with red). It appears space and dash are ignored in VLOOKUP.

Any idea how to solve these anomalies?
Thanks.

 

[RobertSF]

Your formula is =VLOOKUP(F3, Ygroup, 3, TRUE). That last argument is TRUE or FALSE.

If it's TRUE, VLOOKUP will find an approximate match, which is what's happening in your case. If you want to find an exact match, just change TRUE to FALSE.

(As far as I know, VLOOKUP doesn't ignore any characters).

 

[FDibbins]

Don't know if you tried to post some sample data, but if you did, it didn't come through.

Can you post some sample data and show what you have tried?

 

[Csibész]

Don't know if you tried to post some sample data, but if you did, it didn't come through.

Can you post some sample data and show what you have tried?

Hello,

Looks like the embedded image did not make it. This link should work:
https://s20.postimg.org/3k9kknk19/excelpture.jpg
Let me know.
Thanks

 

[FDibbins]

I am not permitted to follow links

Can you show ( here) a sample of what you are working with, and what formula you are using?

 

[Csibész]

I am not permitted to follow links
Can you show ( here) a sample of what you are working with, and what formula you are using?

Oh, that's too bad... I'll try to type it in...

..A.......B.......C.....D..E...F................G
1 Limits table (Ygroup)....Results..............expected
2 from....to&incl id#......id# author...........id#
3 Y.......Yaz.....10.......70..Young-collection 60
4 Yb......Yd......20.......10..Yamamoto.........10
5 Ye......Yn......30.......60..Young Chris......50
6 Yo......Youm....40.......70..Youngabond V.G...70
7 Young A Young Z 50.......80..Yuille Cory......80
8 Young...........60.......20..Ybsen............20
9 Younga..Yr......70
0 Ys......Yul.....80
1 Yum.....Yz......90

in column E: =VLOOKUP(F3,Ygroup,3,TRUE)


After this "minor" exercise, ahem, may I suggest you to turn on image viewing? Life would be simpler and more colourful...

 

[RobertSF]

Did you try my answer? I believe it's correct. You must use FALSE in your VLOOKUP if you want exact matches.

 

[Csibész]

Did you try my answer? I believe it's correct. You must use FALSE in your VLOOKUP if you want exact matches.

You would be correct if an exact match was to be had. In my case it is a string that needs to find it's slot between two string limits. So it has to be set TRUE. And true to form with FALSE I get the familiar #N/A as a result.

For example, take row 7:
7 Young A Young Z 50
My limits are Young A and Young Z. Therefore, any string that falls between these two should take the value of 50. Young Chris would, of course, belong to this set, yet instead of 50 the VLOOKUP function assigns 60 to the record, which is incorrect.

I would like to avoid macros if a function could be used. So, here is hoping that FDibbins has an answer that both of us will find useful...

 

[AhoyNC]

One thing to note is that if you use TRUE in VLOOKUP your data table "Limits table" has to be sorted in ascending order.

 

[RobertSF]

Darn, I didn't catch that detail! Yes, let's hope there's a solution.