Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for a code, and have the correct information auto fi
So, here is the basic problem - I have one spreadsheet that has 100's of
codes, and information below each code. I have another spreadsheet thats called a C of A (Certificate of Analysis). This spreadsheet needs only some of the information on the 1st sheet. I want to make it so I can simply type in the code on the 2nd sheet, and the required information will automatically fill in. I think I need to use a LOOKUP formula, but I am struggling to make this work. Any assistance or suggestions would be appreciated =) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for a code, and have the correct information auto fi
Jared,
Since you say that your spreadsheet has 100s of codes and information BELOW each code, it sounds like HLOOKUP() is what you want to use. The best help I can give is to explain in more detail than Excel's Help does about how HLOOKUP() works. HLOOKUP() has 3 mandatory parameters and 1 optional one: what to look for table to look for it in the left-most column of the table which column from the table to return information when a match is found [optional] TRUE if the lookup column has to be in order, FALSE if not. Lets say your codes are in row 1 and are in columns A through IA (so their address would be A1:IA1. But you have 4 rows of information below them, so the whole table has an address of A1:IA4. And to keep the address from changing as we move the formula around we would write that as $A$1:$IA$4 You said the second sheet's name is C of A, but didn't mention the first sheet's name, so I'll assume it is "Source Sheet". Some where on [C of A] put in one of your codes, lets say you put that in cell A2. In another cell put this formula: =HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False) That should return the value of the matching code on Source Sheet that is in row 2 (the 2nd row of the table). Change the , 2, to , 3, and you'll get the information from the 3rd row, and , 4, would return the information from the 4th row. Now, if you enter a code that doesn't match one in the table on Source Sheet, the formula will return the #N/A error. You can hide that by nesting the formula within a test for that specific error as: =IF(ISNA(HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False)), "",HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False)) That all is one formula on one line. What it says that if the lookup doesn't find a match, put an empty string in the cell, but if it does find a match, then return the result we want. Hope this helps you some. P.S. VLOOKUP() works the same way, but uses the 1st column of the table to look in for the matches instead of the 1st row. "Jared" wrote: So, here is the basic problem - I have one spreadsheet that has 100's of codes, and information below each code. I have another spreadsheet thats called a C of A (Certificate of Analysis). This spreadsheet needs only some of the information on the 1st sheet. I want to make it so I can simply type in the code on the 2nd sheet, and the required information will automatically fill in. I think I need to use a LOOKUP formula, but I am struggling to make this work. Any assistance or suggestions would be appreciated =) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for a code, and have the correct information auto fi
Third try to post an answer!!
Jared, Since you say that your spreadsheet has 100s of codes and information BELOW each code, it sounds like HLOOKUP() is what you want to use. The best help I can give is to explain in more detail than Excel's Help does about how HLOOKUP() works. HLOOKUP() has 3 mandatory parameters and 1 optional one: what to look for table to look for it in the left-most column of the table which column from the table to return information when a match is found [optional] TRUE if the lookup column has to be in order, FALSE if not. Lets say your codes are in row 1 and are in columns A through IA (so their address would be A1:IA1. But you have 4 rows of information below them, so the whole table has an address of A1:IA4. And to keep the address from changing as we move the formula around we would write that as $A$1:$IA$4 You said the second sheet's name is C of A, but didn't mention the first sheet's name, so I'll assume it is "Source Sheet". Some where on [C of A] put in one of your codes, lets say you put that in cell A2. In another cell put this formula: =HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False) That should return the value of the matching code on Source Sheet that is in row 2 (the 2nd row of the table). Change the , 2, to , 3, and you'll get the information from the 3rd row, and , 4, would return the information from the 4th row. Now, if you enter a code that doesn't match one in the table on Source Sheet, the formula will return the #N/A error. You can hide that by nesting the formula within a test for that specific error as: =IF(ISNA(HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False)), "",HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False)) That all is one formula on one line. What it says that if the lookup doesn't find a match, put an empty string in the cell, but if it does find a match, then return the result we want. Hope this helps you some. P.S. VLOOKUP() works the same way, but uses the 1st column of the table to look in for the matches instead of the 1st row. "Jared" wrote: So, here is the basic problem - I have one spreadsheet that has 100's of codes, and information below each code. I have another spreadsheet thats called a C of A (Certificate of Analysis). This spreadsheet needs only some of the information on the 1st sheet. I want to make it so I can simply type in the code on the 2nd sheet, and the required information will automatically fill in. I think I need to use a LOOKUP formula, but I am struggling to make this work. Any assistance or suggestions would be appreciated =) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for a code, and have the correct information auto fi
Jared,
Since you say that your spreadsheet has 100s of codes and information BELOW each code, it sounds like HLOOKUP() is what you want to use. The best help I can give is to explain in more detail than Excel's Help does about how HLOOKUP() works. HLOOKUP() has 3 mandatory parameters and 1 optional one: what to look for table to look for it in the left-most column of the table which column from the table to return information when a match is found [optional] TRUE if the lookup column has to be in order, FALSE if not. Lets say your codes are in row 1 and are in columns A through IA (so their address would be A1:IA1. But you have 4 rows of information below them, so the whole table has an address of A1:IA4. And to keep the address from changing as we move the formula around we would write that as $A$1:$IA$4 You said the second sheet's name is C of A, but didn't mention the first sheet's name, so I'll assume it is "Source Sheet". Some where on [C of A] put in one of your codes, lets say you put that in cell A2. In another cell put this formula: =HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False) That should return the value of the matching code on Source Sheet that is in row 2 (the 2nd row of the table). Change the , 2, to , 3, and you'll get the information from the 3rd row, and , 4, would return the information from the 4th row. Now, if you enter a code that doesn't match one in the table on Source Sheet, the formula will return the #N/A error. You can hide that by nesting the formula within a test for that specific error as: =IF(ISNA(HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False)), "",HLOOKUP(A2,'Source Sheet'!$A$1:$IA$4, 2, False)) That all is one formula on one line. What it says that if the lookup doesn't find a match, put an empty string in the cell, but if it does find a match, then return the result we want. Hope this helps you some. P.S. VLOOKUP() works the same way, but uses the 1st column of the table to look in for the matches instead of the 1st row. "Jared" wrote: So, here is the basic problem - I have one spreadsheet that has 100's of codes, and information below each code. I have another spreadsheet thats called a C of A (Certificate of Analysis). This spreadsheet needs only some of the information on the 1st sheet. I want to make it so I can simply type in the code on the 2nd sheet, and the required information will automatically fill in. I think I need to use a LOOKUP formula, but I am struggling to make this work. Any assistance or suggestions would be appreciated =) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for a code, and have the correct information auto fi
Well, sorry about the triple post - damned HTML side of this mess kept
telling me that it wasn't posting my response. "Jared" wrote in message ... So, here is the basic problem - I have one spreadsheet that has 100's of codes, and information below each code. I have another spreadsheet thats called a C of A (Certificate of Analysis). This spreadsheet needs only some of the information on the 1st sheet. I want to make it so I can simply type in the code on the 2nd sheet, and the required information will automatically fill in. I think I need to use a LOOKUP formula, but I am struggling to make this work. Any assistance or suggestions would be appreciated =) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying concatenate not returning correct cell information | Excel Discussion (Misc queries) | |||
#Value! error on code that should be correct? | Excel Discussion (Misc queries) | |||
How to see the correct information from file without breaking links | Links and Linking in Excel | |||
Auto Correct ? | Excel Discussion (Misc queries) | |||
auto correct | Excel Discussion (Misc queries) |