using functions without sorting my columns first

J

Joshua

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have 2 tables and the values in the 1st column of each table are the same, but in differing orders. What I want to do is enter a formula column into the 1st table that uses the first column of the two tables like a key to pick a cell from a different column in the second table.

The functions I've found which appear to do this are LOOKUP, VLOOKUP, and MATCH. My problem lies in that all 3 require the column of table 1 to be sorted in descending order. I can't do that, the order is fixed and rational, but not in descending order.

Once I get a working formula I plan to copy and past the formula into 20+ tables, and having to manually sort any of the columns will defeat the purpose of using a function to do the heavy lifting for me. It would take the same amount of time to just copy and past the values myself.

Is their a way to get around this need for the columns to be sorted?
 
N

Niek Otten

Forget about LOOKUP.
But VLOOKUP and HLOOKUP do not require the table to be sorted, if the fourth argument is set to FALSE.
BTW, if it's not set to FALSE, the table has to be sorted ascending, not descending.

Here's a good tutorial about VLOOKUP:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Version: 2008
| Operating System: Mac OS X 10.5 (Leopard)
| Processor: Intel
|
| I have 2 tables and the values in the 1st column of each table are the same, but in differing orders. What I want to do is enter
a formula column into the 1st table that uses the first column of the two tables like a key to pick a cell from a different column
in the second table.
|
| The functions I've found which appear to do this are LOOKUP, VLOOKUP, and MATCH. My problem lies in that all 3 require the
column of table 1 to be sorted in descending order. I can't do that, the order is fixed and rational, but not in descending order.
|
| Once I get a working formula I plan to copy and past the formula into 20+ tables, and having to manually sort any of the columns
will defeat the purpose of using a function to do the heavy lifting for me. It would take the same amount of time to just copy and
past the values myself.
|
| Is their a way to get around this need for the columns to be sorted?
 
R

rschaffer

I am switching from a PC to Mac.
Where is button/icon to left, right or center column?
Why doesnt what I type appear above columns line on my Pc?
 
J

Joshua

There are several ways.

1. Go to the menu bar and select View>Formatting Palette. This inspector window has buttons for controlling column alignment.

2. Or, you can use the keyboard shortcuts
Apple + R = Right justified
Apple + L = Left justified
Apple + E = Centered

3. Or, you can go to the menu bar and select View>Toolbars>Formatting, and it'll place several icons at the top of the window for controlling alignment as well as other options for formatting.
 
C

CyberTaz

This one remained unanswered :)


Why doesnt what I type appear above columns line on my Pc?

You're referring to the Formula Bar which for some totally irrational reason
- and in this version of Excel only - has been unalterably severed from the
document window altogether.

If it isn't displayed at all go to the View menu & select Formula Bar to
turn it on. If it's already checked scour your display's viewing area & you
should find the Formula Bar floating around *somewhere*.

HTH |:>)
Bob Jones
[MVP] Office:Mac
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top