Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
have a spreadsheet that contains serial numbers in one column. I would like to create a formula in another column such that when I enter a specifc serial number, then a product name would be entered into another column.
So, for example, let's say the serial number was 001 and the product was Apple. Then for every 001 that I enter or that Excel finds in column A, Column B would display the word Apple.
I would like to do this for 17 serial numbers...
I asked this in another area, and I believe 7 was the limit. Is there a way I can get around this?
Thanks
Razor2.3:
Did you notice that the syntax at the techonthenet site doesn't match the Excel Help files? Specifically, I'm referring to the names for the VLOOKUP arguments.
Perhaps it would eliminate any confusion if you provided links directly to MS's online help files...unless you really hate MS that much. <g>
At least that way to user would see the same syntax as that used within Excel itself, such as the help text that pops up as you are entering the formula.
0 
DerbyDad03: unless you really hate MS that much.
Honestly, if it's not on MSDN, I'm probably not going to see it.
0
re: Honestly, if it's not on MSDN, I'm probably not going to see it.
I don't understand. How does that relate to posting links to MS help sites instead of techonthenet?
0
DerbyDad03: How does that relate to posting links to MS help sites instead of techonthenet?
For MS, I first check MSDN. If I don't find something, I just Google it. techonthenet was the first hit, and its information was valid.Also, I'm honestly not seeing an appreciable difference between the two links.
0
re: I'm honestly not seeing an appreciable difference between the two links
I guess it depends on your definition of appreciable. <g>
techonthenet uses what I can only assume are their own names for the VLOOKUP arguments:
- value
- table_array
- index_number
- not_exact_matchMaybe it's a copyright issue.
The MS site uses the names that a user would find in the Excel Help files:
- lookup_value
- table_array
- col_index_num
- range_lookupIf people are discussing/describing the function in a forum or an email, it might get confusing it both parties are using different names for the arguments.
"Set the range_lookup argument to 0"
"What range_lookup argument? I'm looking at the syntax at techonthenet and I don't see that argument included in the function description."
Since MS "owns" the function, I think their argument names should be the ones to use.
re: techonthenet was the first hit, and its information was valid
Interesting. If I Google vlookup, the MS site is first, techonthenet is second.
0
DerbyDad03: Since MS "owns" the function, I think their argument names should be the ones to use.
Or we could just go by the argument positions, and avoid all confusion. :P
0
Hi,
To use Vlookup, use two columns for your 17 serial numbers and descriptions, they can be on another sheet in your workbook so that they don't interfere with your data input.
Column A is headed serial number and column B is headed description (the actual names don't matter).
The order does matter - the value you use to search for a description must be to the left of the description, e.g., Serial numbers in column A and descriptions in column B.Create all 17 pairs of serial numbers and descriptions.
Lets say this is on Sheet2 in the range Sheet2!A2:B18 (17 pairs)On sheet 1 if you enter a serial number in cell C3, put the following formula in cell D3:
=VLOOKUP(C3,Sheet2!$A$2:$B$18,2)
Note the $ signs in the reference to your range of data pairs.
This will allow you to drag the formula down from D3, but still refer to the same range of data pairs.
The '2' at the end of the formula is because the result of your lookup is in the second column of the data pairs.This should get you started.
If your serial numbers in the data pairs are not in numerical order you will have to add another parameter to the Vlookup function - see the help file (lots of info on help files already !)
A quick way to make creating your vlookup formula easier is to type ='Vlookup( ' in the cell and then click on the fx button next to the formula bar. (You need to at least enter the first '(' after the function name for this to work)
In the box that comes up click in each of the sub-boxes to get extra descriptions about each part of the formula.Regards
0
Hi,
Looking again at the formula I suggested, it would be better if it was
=VLOOKUP(C3,Sheet2!$A$2:$B$18,2,FALSE)
False means there must be an exact match.
Without it, if you enter a serial number that isn't in the table, it will return the last description it finds.Hope it works OK for you
Regards
0  |
 Renaming folder in VB scr...
|
Slooooow scanning
|
| Login or Register to Reply | |
| Login | Register |
| Ads by Google |
