Excel What Function Do I Use? from Mr Excel



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

What Function Do I Use?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

matthewparry45
Board Regular


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

Status: Offline

 Reply with quote  

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!

Post Wed Apr 28, 2004 10:36 pm 
 View user's profile Send private message Send e-mail

Aladin Akyurek
.


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

Status: Offline

 Reply with quote  

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
=

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,"")

Post Wed Apr 28, 2004 10:43 pm 
 View user's profile Send private message

matthewparry45
Board Regular


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

Status: Offline

 Reply with quote  

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?

Post Wed Apr 28, 2004 10:54 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


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

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.