[SOLVED] Lookup function

[Skaven]

Hi there,

I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

Any help you could provide would be appreciated

Thanks,

Joanne

[ChrisMattock]

This should work:

=VLOOKUP(A2,'Active Parts'!$A$2:$B$9,2,FALSE)

EDIT:

If you want to prevent the #N/A error then use this:

=IF(ISNA(VLOOKUP(A2,'Active Parts'!$A$2:$B$9,2,FALSE)),"NA",VLOOKUP(A2,'Active Parts'!$A$2:$B$9,2,FALSE))

[martindwilson]

try
=VLOOKUP(A2,'Active Parts'!$A:B,2,FALSE) instead

[Skaven]

Perfect, thank you!!!!!

This should work:

=VLOOKUP(A2,'Active Parts'!$A$2:$B$9,2,FALSE)

EDIT:

If you want to prevent the #N/A error then use this:

=IF(ISNA(VLOOKUP(A2,'Active Parts'!$A$2:$B$9,2,FALSE)),"NA",VLOOKUP(A2,'Active Parts'!$A$2:$B$9,2,FALSE))