Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the VLOOKUP feature #2!
I posted this earlier today and got one response, but was still not able to
figure out how to do it. Could someone read over what was said and see if they can explain it a little easier to understand? Thanks!! | "Tricia LeAnn" wrote in message ... || I've read a couple of articles on how to do it, but I still don't quite || understand. This is what I need to do: || Drawing # Part # Tube # || 350TT-77A 100587 TUBE 1A || 350TT-157B 100039 TUBE 1A || 350TT-157C 104287 TUBE 1A || 350TT-133A 100048 TUBE 1A || || Here is what my spreadsheet looks like. I need to be able to find out what || tubes drawings and parts are in. I would like to be able to search by either || the drawing, or the part number. Could somone please tell me how to do this? || (Keep in mind that you are explaining this to a BLONDE!! :) ) || || -- || Have a fantastic day! || || Tricia LeAnn | "Niek Otten" wrote in message ... | Hi Tricia, | | With the Drawing# to look up in D1: | | =VLOOKUP(D1,$A$2:$C$5,3,FALSE) | | With the Part# in D2: | | =VLOOKUP(D2,$B$2:$C$5,2,FALSE) | | I assumed your data with a header in A2:C5 | Kind regards, | | Niek Otten | Microsoft MVP - Excel | "Niek Otten" wrote: <with a header in A2:C5 Should be: with a header in A1:C5 -- Kind regards, Niek Otten Microsoft MVP - Excel Ok, I hate to be a pain, but I'm sure people who use excel regularly would understand that completely, but I'm still having some trouble. I'll give you my spreadsheet information in more detail, and maybe you can help me then. A1- PROJECT: Quickfind Tube Index! A3- DrAwInGs iN TUBE: 1A (A5)DRAWING # (B5)PART # (e5)Tube # (A6)350TT-8A & 9A (B6)104597 (E6)TUBE 1A (A7)350TT-29A (B7)100011 (E7)TUBE 1A (A8)350TT-33 (B8)103220 (E8)TUBE 1A (A9)350TT- 50A (B9)100019 (E9)TUBE 1A (A10)350TT-62 (B10)100024 (E10)TUBE 1A What I'm trying to do is make a searchable database where I can find out which tubes certain drawings and part numbers are located in. I have already entered all of the information, and there are about 4,500 different corresponding drawing and part numbers that I have in the system. I would like to be able to just type in "100024" and find out that it is located in TUBE 1A. Does that make more sense? Again, I am sorry to be such a hassle, but I really do appreciate your help! -- Have a fantastic day! Tricia LeAnn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the VLOOKUP feature #2!
One way using index/match
With the table as posted, Inputs in G2 down, eg: 100024 Place in H2: =INDEX(E:E,MATCH(G2,B:B,0)) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tricia LeAnn" wrote: Ok, I hate to be a pain, but I'm sure people who use excel regularly would understand that completely, but I'm still having some trouble. I'll give you my spreadsheet information in more detail, and maybe you can help me then. A1- PROJECT: Quickfind Tube Index! A3- DrAwInGs iN TUBE: 1A (A5)DRAWING # (B5)PART # (e5)Tube # (A6)350TT-8A & 9A (B6)104597 (E6)TUBE 1A (A7)350TT-29A (B7)100011 (E7)TUBE 1A (A8)350TT-33 (B8)103220 (E8)TUBE 1A (A9)350TT- 50A (B9)100019 (E9)TUBE 1A (A10)350TT-62 (B10)100024 (E10)TUBE 1A What I'm trying to do is make a searchable database where I can find out which tubes certain drawings and part numbers are located in. I have already entered all of the information, and there are about 4,500 different corresponding drawing and part numbers that I have in the system. I would like to be able to just type in "100024" and find out that it is located in TUBE 1A. Does that make more sense? Again, I am sorry to be such a hassle, but I really do appreciate your help! -- Have a fantastic day! Tricia LeAnn |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the VLOOKUP feature #2!
Tricia LeAnn,
By default, you will not be able to use one cell to enter a part # or drawing # to search for. XL will not be able to determine which one it is (...not to mention the formulas are different for each one). You can use 2 different cells (1 for part #'s and 1 for drawing #'s) or you can use 2 different cells (1 for part/drawing #'s and one to tell what kind of number it is. I'm gonna describe the 2nd one. I'm gonna assume you want to enter the part/drawing # you want to search for in cell A2 and what type of number it is in cell B2. I would create a data validation on cell B2. I would make it a List DV and type in the 2 different values. I'll use "Part Number" and "Drawing Number". Where do you want the results to be? I'm gonna assume cell B3. In B3, enter this formula: =IF(B2="Part Number",VLOOKUP($A$2,$A$6:$E$10,5,0),IF(B2="Drawin g Number",VLOOKUP($A$2,$B$6:$E$10,4,0),B2 & " does not exist or wrong type of number")) Expand your lookup range as necessary. I have not tested this, but I think it will work. Some one else might come up with a more creative & efficient way of doing this. HTH, Conan "Tricia LeAnn" wrote in message ... I posted this earlier today and got one response, but was still not able to figure out how to do it. Could someone read over what was said and see if they can explain it a little easier to understand? Thanks!! | "Tricia LeAnn" wrote in message ... || I've read a couple of articles on how to do it, but I still don't quite || understand. This is what I need to do: || Drawing # Part # Tube # || 350TT-77A 100587 TUBE 1A || 350TT-157B 100039 TUBE 1A || 350TT-157C 104287 TUBE 1A || 350TT-133A 100048 TUBE 1A || || Here is what my spreadsheet looks like. I need to be able to find out what || tubes drawings and parts are in. I would like to be able to search by either || the drawing, or the part number. Could somone please tell me how to do this? || (Keep in mind that you are explaining this to a BLONDE!! :) ) || || -- || Have a fantastic day! || || Tricia LeAnn | "Niek Otten" wrote in message ... | Hi Tricia, | | With the Drawing# to look up in D1: | | =VLOOKUP(D1,$A$2:$C$5,3,FALSE) | | With the Part# in D2: | | =VLOOKUP(D2,$B$2:$C$5,2,FALSE) | | I assumed your data with a header in A2:C5 | Kind regards, | | Niek Otten | Microsoft MVP - Excel | "Niek Otten" wrote: <with a header in A2:C5 Should be: with a header in A1:C5 -- Kind regards, Niek Otten Microsoft MVP - Excel Ok, I hate to be a pain, but I'm sure people who use excel regularly would understand that completely, but I'm still having some trouble. I'll give you my spreadsheet information in more detail, and maybe you can help me then. A1- PROJECT: Quickfind Tube Index! A3- DrAwInGs iN TUBE: 1A (A5)DRAWING # (B5)PART # (e5)Tube # (A6)350TT-8A & 9A (B6)104597 (E6)TUBE 1A (A7)350TT-29A (B7)100011 (E7)TUBE 1A (A8)350TT-33 (B8)103220 (E8)TUBE 1A (A9)350TT- 50A (B9)100019 (E9)TUBE 1A (A10)350TT-62 (B10)100024 (E10)TUBE 1A What I'm trying to do is make a searchable database where I can find out which tubes certain drawings and part numbers are located in. I have already entered all of the information, and there are about 4,500 different corresponding drawing and part numbers that I have in the system. I would like to be able to just type in "100024" and find out that it is located in TUBE 1A. Does that make more sense? Again, I am sorry to be such a hassle, but I really do appreciate your help! -- Have a fantastic day! Tricia LeAnn |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the VLOOKUP feature #2!
The two Vlookups are doing what you want. Assuming your drawing numbers are
in A2:A5, part numbers are in B2:B5 and tube numbers are in C2:C5, an array A2:C5 is formed. The first vlookup uses the array A2:C5 to look up the drawing number in D1 in column 1 (A2:A5) of the array.for an exact match (the 'FALSE") in the vlookup function. If a match is found, it returns the corresponding entry from column 3 (C2:C5) of the array - the tube number. That's what the "3" is in the vlookup - return column 3. If no match is found the vlookup returns #N/A. The second vlookup functions uses the array B2:C5 and looks up D2 (the part number) in column one (B2:B5) of the array and if an exact match is found, returns the corresponding value in column 2 of the array C2:C5 - the tube number That's what the "2" is in the vlookup - return column 2. As in the first lookup, if there is no match #N/A is returned. The two vlookups allow you to look up the tube number by using either drawing number or part number. Tyro "Tricia LeAnn" wrote in message ... I posted this earlier today and got one response, but was still not able to figure out how to do it. Could someone read over what was said and see if they can explain it a little easier to understand? Thanks!! | "Tricia LeAnn" wrote in message ... || I've read a couple of articles on how to do it, but I still don't quite || understand. This is what I need to do: || Drawing # Part # Tube # || 350TT-77A 100587 TUBE 1A || 350TT-157B 100039 TUBE 1A || 350TT-157C 104287 TUBE 1A || 350TT-133A 100048 TUBE 1A || || Here is what my spreadsheet looks like. I need to be able to find out what || tubes drawings and parts are in. I would like to be able to search by either || the drawing, or the part number. Could somone please tell me how to do this? || (Keep in mind that you are explaining this to a BLONDE!! :) ) || || -- || Have a fantastic day! || || Tricia LeAnn | "Niek Otten" wrote in message ... | Hi Tricia, | | With the Drawing# to look up in D1: | | =VLOOKUP(D1,$A$2:$C$5,3,FALSE) | | With the Part# in D2: | | =VLOOKUP(D2,$B$2:$C$5,2,FALSE) | | I assumed your data with a header in A2:C5 | Kind regards, | | Niek Otten | Microsoft MVP - Excel | "Niek Otten" wrote: <with a header in A2:C5 Should be: with a header in A1:C5 -- Kind regards, Niek Otten Microsoft MVP - Excel Ok, I hate to be a pain, but I'm sure people who use excel regularly would understand that completely, but I'm still having some trouble. I'll give you my spreadsheet information in more detail, and maybe you can help me then. A1- PROJECT: Quickfind Tube Index! A3- DrAwInGs iN TUBE: 1A (A5)DRAWING # (B5)PART # (e5)Tube # (A6)350TT-8A & 9A (B6)104597 (E6)TUBE 1A (A7)350TT-29A (B7)100011 (E7)TUBE 1A (A8)350TT-33 (B8)103220 (E8)TUBE 1A (A9)350TT- 50A (B9)100019 (E9)TUBE 1A (A10)350TT-62 (B10)100024 (E10)TUBE 1A What I'm trying to do is make a searchable database where I can find out which tubes certain drawings and part numbers are located in. I have already entered all of the information, and there are about 4,500 different corresponding drawing and part numbers that I have in the system. I would like to be able to just type in "100024" and find out that it is located in TUBE 1A. Does that make more sense? Again, I am sorry to be such a hassle, but I really do appreciate your help! -- Have a fantastic day! Tricia LeAnn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the VLOOKUP feature. | Excel Discussion (Misc queries) | |||
Using Comment Feature | Excel Discussion (Misc queries) | |||
Zoom Feature | Excel Discussion (Misc queries) | |||
Yes / No Feature | Excel Worksheet Functions | |||
Count feature | Excel Discussion (Misc queries) |