Instead of 200*16fl, use 200~*16fl
and the VLookup should work correctly.

Clarence Crow wrote:
Hello again all.

I haven't posted here for ages, but something weird happened the other
day at work when my Boss said there was an inconsistency in the
"Steel Database", (in another workbook), we use to VLOOKUP for Data
associated with Steel Sections.
BTW, I'm from au and we work in Metric, in case some of you Yanks
can't savvy what I'm on about.

He demonstrated that by Typing 200x6fl in one row and 200*6fl in the
next row, different DATA were returned in each row for the Unit Mass,
Unit Surface Area and Unit Cost.

I then perused the "Steel Database" and all of these Steel Sections
were entered in it as nnnxn and not nnn*n, but that didn't solve the
entire problem. Apparently when doing a VLOOKUP, Excel sees an
asterisk "*" to the left of a number, it treats it as a WildCard and
VLOOKUPs the next closest number to be found. In this case, for
200*6fl, it returned all the Data for 200x16fl which was further down
the Table and much heavier.
Plus I typed in 200x16fl AND 200*16fl and the same DATA were returned?

So I told him to adhere to the Format of the "Steel Database".

How can I "child-proof/boss-proof" this setup?


-- "if you can see it coming, head it off at the pass, else put the wagons in a circle"
-- Please reply to this ng as:
-- my email adress is 100% faked to prevent proliferation of SPAM!!
-- Regards

-- Clarence Crow


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html