Finding an item in a list & returning a specific value

  • Thread starter Thread starter SBW
  • Start date Start date
S

SBW

Is there a function I can use to ....

- Check if Sheet 1 L1 value is listed anywhere in Sheet 2 Col A. If true,
then return the corresponding value from Sheet 2 Col B into the corresponding
cell in Sheet 1 Col M.

Sheet 1:
A....K L M
1 Part 1
2 Part 2
3 Part 3 4/16/2009

Sheet 2:
A B
1 Part 3 4/16/2009
2 Part 4
3 Part 5 4/17/2009
 
Hi,

Put this in M1 on Sheet 1 and drag down as required. If your returning dates
the column will need to be formatted as date

=IF(COUNTIF(Sheet2!A1:A200,L1)>0,VLOOKUP(L1,Sheet2!A1:B200,2,FALSE),"")

Mike
 
=INDEX(Sheet2!$A$1:$B$16,MATCH(L1,Sheet2!$A$1:$A$16,0),2)

Returns #N/A if part not found... use the standard ISERROR and IF around the
formula.
 
If the value to be returned is a date and the part will only appear once:

=SUMIF(Sheet2!A1:A100,L1,Sheet2!B1:B100)

Format as Date

A result of 0 means either the part is not present or the part is present
but there is no date for that part.
 
Hi,

Thank you for the solution.

The only issue I am having is the returned value is either a date, N/A or a
blank field. For a returned N/A value, when I look-up (utilize 'Find') the
part number in Sheet B and then return to Sheet A, the correct date value
then appears in Sheet A for the correct part number. Any idea why the
calculation appears to break from time to time?

This also occurs in the solution Teethless mama provided.
 
Hi,

Thank you for the solution.

The only issue I am having is the returned value is either a date, N/A or a
blank field. For a returned N/A value, when I look-up (utilize 'Find') the
part number in Sheet B and then return to Sheet A, the correct date value
then appears in Sheet A for the correct part number. Any idea why the
calculation appears to break from time to time?

This also occurs with the solution Mike H provided. I sent the same reply to
Mike H.
 
Back
Top