Excel What Function Do I Use? from Mr Excel
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 What Function Do I Use?

matthewparry45
Board Regular

Joined: 22 Jul 2002
Posts: 16
Location: Matthew Parry
Flag:

Status: Offline

What Function Do I Use?

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!

Wed Apr 28, 2004 10:36 pm

.

Joined: 15 Feb 2002
Posts: 15262
Location: The Hague
Flag:

Status: Offline

Re: What Function Do I Use?

 Microsoft Excel - Book8 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 C2C3C4C5C6C7C8C9C10C11 =

A
B
C
D
1
1
2
1AA
3
2B

4
1CC
5
3K

6
3L

7
4M

8
5N

9
6O

10
7P

11
8Q

12

 Sheet1

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The formula in C2 is:

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

Wed Apr 28, 2004 10:43 pm

matthewparry45
Board Regular

Joined: 22 Jul 2002
Posts: 16
Location: Matthew Parry
Flag:

Status: Offline

Re: What Function Do I Use?

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

Wed Apr 28, 2004 10:54 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum