Help with hlookup formula

[barbara_retzlaff]

I haven't worked with Excel much, but I'm guessing what I want to do is
possible. I have two sheets in a workbook. One lists about 100 serial
numbers. The other lists about 5000 serial numbers with additional
information on each. So what I want to do is filter and see only the
matches between the two sheets. It looks like you might be able to do
it with hlookup, but I can't figure out the syntax. Is this possible?
Thanks so much.

 

[Pete_UK]

VLOOKUP would probably be more suitable. Assuming your sheet with the
5000 serial numbers is called Master and has data starting in row 2
with a header row, and assume your serial numbers are in column A with,
say, 4 other columns of additional information that you want to bring
to the second sheet.

On the second sheet you have your 100 serial numbers in column A and
you want against each of these the four additional columns of
information from the Master sheet which correspond to the appropriate
serial number. Enter this formula in B2, assuming the same header row
as in the master sheet:

=VLOOKUP($A2,Master!$A$2:$E$5000,2,0)

If there is a matching serial number in the master sheet, this will
bring the corresponding item of data from the second column of your
main table (governed by the 2 near the end of the formula. This formula
can be copied to C2, D2 and E2, and it will return the same data -
however, change the 2 to a 3 in C2, the 2 to a 4 in D2, and the 2 to a
5 in E2 and you will have all the corresponding data for that serial
number. Your formulae should look like:

C2: =VLOOKUP($A2,Master!$A$2:$E$5000,3,0)
D2: =VLOOKUP($A2,Master!$A$2:$E$5000,4,0)
E2: =VLOOKUP($A2,Master!$A$2:$E$5000,5,0)

You can then copy all 4 formulae down to row 100 to give you matching
data for each of your serial numbers.

However, if a serial number does not have an exact match in the master
table, then the formulae will return #N/A. If you would prefer some
message like "Not present" instead, you should amend the formula in B2
as follows:

=IF(ISNA(VLOOKUP($A2,Master!$A$2:$E$5000,1,0)),"Not
present",VLOOKUP($A2,Master!$A$2:$E$5000,2,0))

Again, this formula can be copied to C2:E2 and you will need to change
the final 2 to 3, 4 and 5 as above, before copying down.

Hope this helps.

Pete

 

[Pete_UK]

Thanks for the feedback, Barbara.

Glad to be of help. You might like to try this variation in C2 of the
second
sheet:

=IF($B2="Not present","",VLOOKUP($A2,Master!$A$2:$E$5000,3,0))

then copy this to D2 and E2 and change the 3 near the end to 4 and 5
respectively. This will return a blank to these cells if the serial
number
is not found, rather than having four "Not present" messages.

Hope this is even better for you.

I don't know where your first post went to - seems to have disappeared!

Pete