Excel Problem

[MKSheppard]

So. I have a problem with Excel 2010. I am trying to do a VLOOKUP of stuff to assist me in my research at the National Archives.



As you can see, a lot of interesting things are in RG-306-PS-C

Now. There is a finding aid for what numbers go into what boxes, which I scanned in and OCRed.



Now if we take the top most one; photo ID 58-5134 "Three Stage SSTO by Goodyear"; if you check the box index, the correct box that holds 58-5134 is Box 12.

But VLOOKUP renders a totally wrong box; 20B.

This has me fucking tearing my hair out; since automating the lookup speeds up my research turnaround considerably; yet Excel is being fucking retarded.

[General Zod]

Set the range to False instead of True or Excel won't provide an exact match.

[MKSheppard]

Look at the first photo. It shows teh VLOOKUP.

But since you didn't notice:

=VLOOKUP(A2,Rows!A$2:B$87,TRUE)

[General Zod]

Look at the first photo. It shows teh VLOOKUP.

But since you didn't notice:

=VLOOKUP(A2,Rows!A$2:B$87,TRUE)

Read my edit. I noticed after the fact.

[MKSheppard]

gives me #Value!

[General Zod]

gives me #Value!

Trim clean your entries. Then highlight your cells, right-click, format cells, and select text. If they're using some funky formatting or there's hidden spaces it won't match up either.

[MKSheppard]

Trim clean your entries. Then highlight your cells, right-click, format cells, and select text. If they're using some funky formatting or there's hidden spaces it won't match up either.

Done that. No spaces are in my entries. Still #VALUE!

[Terralthra]

VLOOKUP will not work with ranges such as you have with TRUE set as Zod says, since none of your ID numbers (Column A) in the first table are exact matches to the columns in the second table (Column B or D). That needs to be set to FALSE so it will give you the last LESSTHAN response (default behavior, in other words).

It's giving you 20B for all entries you have because your photo IDs are in neither case actual numbers, and Excel's logic for matching strings of numeric data with non-equal lengths and dashes randomly inserted is screwy. In essence, to Excel, 58-1234 is < than 51-12345, where in your numbering system, it is presumably not. Thus, your rows are "out of order" to VLOOKUP and any result you get will be bogus.

No way to do this without sanitizing and normalizing your photo IDs to a consistent length/format.

[Twoyboy]

You can do it. Use INDEX and MATCH together. MATCH is far more forgiving of these things than VLOOKUP.

=INDEX(Rows!$A$2:$A$87,MATCH(A2,Rows!$B$2:$B$87,-1),1)

At least I think that will work...