What Function Do I Use?

[matthewparry45]

I am looking for a formula (and, eventually, formulae) that will compare the contents of a cell against a range and produce an answer in a corresponding cell.

For example, Column A contains 10 rows where A1 = 1, A2 = 2 and sop on. Column B contains 10 rows where B1 = A, B2 = B, and so on.

In column C I want to insert a formula that will compare my criterion (a number between 1 and 10) and show me the corresponding letter from that same row.

However, I don't know if a formula exists that can handle repeated numbers (e.g., in the range A1:A10, the number "1" appears three times, but with different letters in the corresponding rows of column B).

In column C, I only want the first reference (from column B) of the "1" to appear, and in Column D the second reference (from column B) to appear, and in column E the third reference from column B to appear.

So, if in the range A1:A10, cells A2, A4 and A6 contain "1", and cell B2 = A, B4 = K and B6 = N, then C2 = A, C4 = K and C6 = K.

This may not be possible, but I figured if anyone knows the answer I'll find them here.

Thanks!

 

[Aladin Akyurek]

Book8
	A	B	C	D
1			1
2	1	A	A
3	2	B
4	1	C	C
5	3	K
6	3	L
7	4	M
8	5	N
9	6	O
10	7	P
11	8	Q
12
Sheet1

The formula in C2 is:

=IF(A2=$C$1,B2,"")

 

[matthewparry45]

Thanks! But is there a formula that can also produce the reults of references that are not repeated as well?