Home |
Search |
Today's Posts |
#1
|
|||
|
|||
a VLOOKUP quirk
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
On Fri, 26 Nov 2004 18:52:07 -0500, Debra Dalgleish
staggered out of the wildeness and muttered: 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 |
#4
|
|||
|
|||
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 wrote: On Fri, 26 Nov 2004 18:52:07 -0500, Debra Dalgleish staggered out of the wildeness and muttered: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
On Mon, 29 Nov 2004 08:31:51 -0500, Debra Dalgleish
advised: 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 |
#6
|
|||
|
|||
On Mon, 29 Nov 2004 08:31:51 -0500, Debra Dalgleish
contemplated and re-advised: 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 |
#7
|
|||
|
|||
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. Clarence Crow wrote: On Mon, 29 Nov 2004 08:31:51 -0500, Debra Dalgleish contemplated and re-advised: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup finds a blank, but returns a zero - HELP! | Excel Discussion (Misc queries) | |||
Vlookup Syntax Error | New Users to Excel | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions | |||
vlookup. | Excel Worksheet Functions |