a VLOOKUP quirk

[Clarence Crow]

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]

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

 

[Clarence Crow]

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

Not a solution or even an explanation, but a "workaround" by
introducing yet another character.

Turn in ya badge LOL



-- "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]

Sorry, I misread your question. To prevent data being returned when the
code isn't an exact match with an entry in the table, you could use
INDEX/MATCH, instead of VLOOKUP. For example:

=INDEX($B$2:$B$13,MATCH(TRUE,EXACT(D3,$A$2:$A$13),0))

entered as an array formula (Ctrl+Shift+Enter)

In this example, the codes are in A2:A13, the number to return are in
B2:B13, and the lookup value is in cell D3.

If the table contains 200x16fl, an entry of 200*16fl in cell D3 will
return #N/A, as will 200X16fl (X is not an exact match to x).

Or, instead of letting the user type a value to match, you could use
data validation to provide a dropdown list of values from the lookup table.

 

[Clarence Crow]

Sorry, I misread your question. To prevent data being returned when the
code isn't an exact match with an entry in the table, you could use
INDEX/MATCH, instead of VLOOKUP. For example:

=INDEX($B$2:$B$13,MATCH(TRUE,EXACT(D3,$A$2:$A$13),0))

entered as an array formula (Ctrl+Shift+Enter)

In this example, the codes are in A2:A13, the number to return are in
B2:B13, and the lookup value is in cell D3.

If the table contains 200x16fl, an entry of 200*16fl in cell D3 will
return #N/A, as will 200X16fl (X is not an exact match to x).

Or, instead of letting the user type a value to match, you could use
data validation to provide a dropdown list of values from the lookup table.

Thanks for that. I'll rewrite it with the new function and drag it
down the 7,000 odd rows.
We find it more expedient to type it in, thanks all the same.
(I actually had something going in Lotus 2.01 DOS whereby you could
pick with a Macro but some of the other guys were happy to type it
in).
It's just this Boss actually did some work when we were short-staffed
and then complained re the anomaly.
Now I can fix his wagon


-- "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

 

[Clarence Crow]

Sorry, I misread your question. To prevent data being returned when the
code isn't an exact match with an entry in the table, you could use
INDEX/MATCH, instead of VLOOKUP. For example:

=INDEX($B$2:$B$13,MATCH(TRUE,EXACT(D3,$A$2:$A$13),0))

entered as an array formula (Ctrl+Shift+Enter)

In this example, the codes are in A2:A13, the number to return are in
B2:B13, and the lookup value is in cell D3.

If the table contains 200x16fl, an entry of 200*16fl in cell D3 will
return #N/A, as will 200X16fl (X is not an exact match to x).

Or, instead of letting the user type a value to match, you could use
data validation to provide a dropdown list of values from the lookup table.

Managed to write the whole above Formula, (with my parameters),and
enter it as an Array, but nothing happened...I just get a #REF in the
row/colum I want the Data to appear.
It may be because I have the 'Steel Database 2004-7.xls'! in a
separate Workbook, as it is so Large, and we have always addressed it
this way with a VLOOKUP function in 3 separate OFFSETS.
????


-- "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]

The formula should work with values in another workbook. A #REF! error
means that the row number returned by the MATCH function isn't in the
INDEX array. Perhaps your references are slightly off.