Want to lookup a certain value in a column, returning the value from the first

Warren Egan

New Member
Joined
May 5, 2014
Messages
2
I've got a very limited and basic knowledge of Excel. There is one main thing I would like to know and a secondary question that isn't as important. I've searched through google, these forums, Excel's help and tried using the insert function button... but can't find what I am after. I am using Windows 7 and Office 2010.

I'm the leader of a guild in a game and wanted to create a spreadsheet to keep track of everything much easier than having to update every little thing manually in a text document.

One formula I've made is:
Code:
=IF(H2>=2500,"Honourable Mention",IF(H2>=1500,"Safe",IF(H2>=500,"Activity Needed",IF(H2>=1,"Serious Risk of Being Kicked",IF(H2=0,"KICK")))))

That has been applied from K2 to K26, with the cell values changing appropriately.
From B2 to B26 is each members username.

I would like to be able to look up "Honourable Mention" in cells K2:K26, then for it to return the usernames from the same row that it occurs, as a list in a different location for easier viewing for others.

The secondary question:
Is it possible to have excel automatically re-order rows based on the values of a specific column?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You can filter by a specific text or value or you can sort by value.

With any cell in the table selected, go to the Data Ribbon at the top and hit the filter button. Each header should now have a dropdown containing a number of filter and sorting options.

Also, perhaps this would be a simpler version of your formula:


Excel 2010
BHK
1NameActivity LevelResult
2John3000Honourable Mention
3Adam2500Honourable Mention
4Eric200Serious Risk of Being Kicked
5Haley1200Activity Needed
6Shaun0KICK
7Carlos4Serious Risk of Being Kicked
8Diana500Activity Needed
Sheet1
Cell Formulas
RangeFormula
K2=IF(H2<>"",LOOKUP(H2,{0,"KICK";1,"Serious Risk of Being Kicked";500,"Activity Needed";1500,"Safe";2500,"Honourable Mention"}),"")


The most optimal solution, however, would be to keep the lookup table in another location, so that it can be referenced, and edited as needed. For example, if you create a new worksheet called Ref (rename the worksheet Ref), and paste these values beginning in cell A1, you can then use the formula below instead. Then you can change the values at any time and all of the cells will update:


Excel 2010
AB
1Activity ThreshholdMessage
20KICK
31Serious Risk of Being Kicked
4500Activity Needed
51500Safe
62500Honourable Mention
Ref



Excel 2010
BHK
1NameActivity LevelResult
2John3000Honourable Mention
3Adam2500Honourable Mention
4Eric200Serious Risk of Being Kicked
5Haley1200Activity Needed
6Shaun0KICK
7Carlos4Serious Risk of Being Kicked
8Diana500Activity Needed
Sheet1
Cell Formulas
RangeFormula
K2=IF(H2<>"",LOOKUP(H2,Ref!$A$2:$B$6),"")
 
Upvote 0
You Ben, are a legend. Thanks a lot for that.
While it isn't what I was expecting, it has the same effect and is more versatile. Working perfectly.
I didn't know the re-ordering and filtering of results would be so simple. Thanks again for teaching me something new.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top