1

In Excel I would like to assign a name to a number based on a separate lists. Example, I have a part number in A1, in D1 I have a title and below it (D2-D11) I have a list of part numbers, in E1 I have another title with a list of part numbers below it, I want the formula in B1 to return the correct title based on what list the part number is in. Thanks for your help

  A        B      C     D          E
1 04     FORMULA       Title   Other Title
2                        01        100
3                        02        101
4                        03        102
5                        04        103
6                        05        105
7                        06        106
8                        07        107
9                        08        108
10                       09        109 
11                       10        110
1
  • 1
    Have you tried anything yourself? Jun 8, 2014 at 13:04

1 Answer 1

1

The function COUNTIF provides a solution: =IF(COUNTIF(D2:D11,A1)>0,D1,IF(COUNTIF(E2:E11,A1)>0,E1,"N/A")). Basically, you are first checking if the list in column D contains the criteria A1 (at least once). If so, return the title in D1, if not, perform the same check with column E.

Alternatively you could create a new table which maps each part to its corresponding list name (assuming each part only occurs in one list). Then you could easily use VLOOKUP on that table.

Note that if you want to copy this formula to cells B2 to B11 you need to (re)name cells used to define the data ranges (D2:D11and E2:E11), e.g. d_begin:d_end and e_begin:e_end.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .