How to search for a code, and have the correct information auto fi

  • Thread starter Thread starter Jared
  • Start date Start date
J

Jared

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 that’s
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

=)
 
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.
 
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,
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.
 
Well, sorry about the triple post - damned HTML side of this mess kept
telling me that it wasn't posting my response.
 
Back
Top