CODEWITHSUNDEEP
excelexcel-formula
hockey2112
hockey2112

Reputation: 506

Formula to add text based on values of two other cells

I have a large spreadsheet that needs a bit of fine-tuning. Column A contains numbers from 1-4000, sequentially-listed. Column B contains some of those same numbers, but many numbers are skipped (for example: 1, 5, 6, 7, 11, 12, 25...); those numbers are not "spaced out" relative to the numbers in column A. Column C contains text entries related to the Column B values. I need to add a formula in column D that will evaluate whether the number in A1 (for example) matches the number in B1, and if it does match it should place the value of C1 in D1. Likewise, if A2 matches B2, then the value of C2 should be placed in D1.

I tried writing some formulas similar to this, but they become far too large when you have to take into account every cell combination: =IF(A1=E1,F1). If I can just get the column B cells to "space themselves out" so that they match up with the cells in column A, that would get me where I need to be. Of course, the Column C data values would need to "follow along" with the Column B values when they are spaced out.

Any ideas on how I can make this happen?

Upvotes: 2

Views: 2140

Answers (1)

chancea
chancea

Reputation: 5958

If the data is like this:

A   B   C
---------------
1   1   Red
2   5   Blue
3   6   Red
4   7   Yellow
5   11  Black
6   12  Green
7   25  Yellow
8       
9       
10      
11      
12      
13      
14      
15      
16      
17      
18      
19      
20      
21      
22      
23      
24      
25

And the desired result is this:

A   B   C       D
--------------------
1   1   Red     Red
2   5   Blue    
3   6   Red
4   7   Yellow
5   11  Black   Blue
6   12  Green   Red
7   25  Yellow  Yellow
8           
9           
10          
11              Black
12              Green
13          
14          
15          
16          
17          
18          
19          
20          
21          
22          
23          
24          
25              Yellow

We can accomplish that using an INDEX-MATCH based formula starting in D1 and auto filling down:

=IFERROR(INDEX(C:C,MATCH(A1,B:B,0)),"")

It will look like this:

A   B   C           D
-----------------------------------------------------------
1   1   Red         =IFERROR(INDEX(C:C,MATCH(A1,B:B,0)),"")
2   5   Blue        =IFERROR(INDEX(C:C,MATCH(A2,B:B,0)),"")
3   6   Red         =IFERROR(INDEX(C:C,MATCH(A3,B:B,0)),"")
4   7   Yellow      =IFERROR(INDEX(C:C,MATCH(A4,B:B,0)),"")
5   11  Black       =IFERROR(INDEX(C:C,MATCH(A5,B:B,0)),"")
6   12  Green       =IFERROR(INDEX(C:C,MATCH(A6,B:B,0)),"")
7   25  Yellow      =IFERROR(INDEX(C:C,MATCH(A7,B:B,0)),"")
8                   =IFERROR(INDEX(C:C,MATCH(A8,B:B,0)),"")
9                   =IFERROR(INDEX(C:C,MATCH(A9,B:B,0)),"")
10                  =IFERROR(INDEX(C:C,MATCH(A10,B:B,0)),"")
11                  =IFERROR(INDEX(C:C,MATCH(A11,B:B,0)),"")
12                  =IFERROR(INDEX(C:C,MATCH(A12,B:B,0)),"")
13                  =IFERROR(INDEX(C:C,MATCH(A13,B:B,0)),"")
14                  =IFERROR(INDEX(C:C,MATCH(A14,B:B,0)),"")
15                  =IFERROR(INDEX(C:C,MATCH(A15,B:B,0)),"")
16                  =IFERROR(INDEX(C:C,MATCH(A16,B:B,0)),"")
17                  =IFERROR(INDEX(C:C,MATCH(A17,B:B,0)),"")
18                  =IFERROR(INDEX(C:C,MATCH(A18,B:B,0)),"")
19                  =IFERROR(INDEX(C:C,MATCH(A19,B:B,0)),"")
20                  =IFERROR(INDEX(C:C,MATCH(A20,B:B,0)),"")
21                  =IFERROR(INDEX(C:C,MATCH(A21,B:B,0)),"")
22                  =IFERROR(INDEX(C:C,MATCH(A22,B:B,0)),"")
23                  =IFERROR(INDEX(C:C,MATCH(A23,B:B,0)),"")
24                  =IFERROR(INDEX(C:C,MATCH(A24,B:B,0)),"")
25                  =IFERROR(INDEX(C:C,MATCH(A25,B:B,0)),"")

Upvotes: 1

Related Questions