Finding the Value furthest Right in a Table

M

MONTPELIERS

New Member
Joined
Aug 18, 2003
Messages
6
I have a table, consisting of 52 columns (52 weeks). In the table i want to update it with changes to employees salarys.

So each row will be a different employee.

eg. columnA= EMPLOYEE NAME, columnD=Week 1, columnE=Week2 etc.

When i change an employees salary i would enter the new salary into the relevant column.

What i want is a formula in columnB that will look along the row and return the value furthest to the right (ie. the most recently updated value).

Anyone help?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
I was thinking if it was possible at all and was almost replying that surely VBA was needed, and then you give such an easy solution... but how does it work? Why does it work? I don't understand it...
 
Upvote 0
Look in the first row or column of an array for the specified value and return a value from the same position in the last row or column of the array.

i.e. it looks for the value lower than 9.99999E+307 and returns the value in the last cell nearest to it. Or the value of the cell before the last blank one in the row.
 
Upvote 0
aha, I see, I was reading the help page for LOOKUP, but was confused. So the Range is considered a 1-dimensional array and the lookup and return values are both in the same (the only) row...
Clever way of solving this, I would have made a macro :p
 
Upvote 0
Hi Hermanito

Here is a larger example of how dynamic LOOKUP's can be used. In this example I am returning the last day worked (column headers) for a number in the various rows. I could in this example increase my data to be number of hours worked per day and also last day a person worked. I use a similar example to this is a targetted sales strategy. Hugely versatile as are MATCH and INDEX if you can get to grips with them.
Book1
ABCDEFGHIJKLM
1
2
3LastWorkingDay
4MondayTuesdayWednesdayThursdayFridaySaturdaySundayHoursLWD
5Bob88824Wednesday
6Dan8868838Saturday
7Mark66Friday
8Ian333312Friday
9
Sheet2


Hope this helps


Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
413
KRice
K
D
  • Question
Applying dupicate Column Headers in an Excel Table
Replies
6
Views
388
awoohaw
awoohaw
C
  • Question
Pivot Table Min and Max values in Column
Replies
0
Views
266
conorrey
C
E
  • Solved
This one is tricky. VLookup but return a different cell value
Replies
6
Views
146
Alex Blakenburg
A
P
  • Question
Transfer data from old sheet to new sheet based on a cell value
Replies
0
Views
171
parsj3
P

Forum statistics

Threads
1,214,668
Messages
6,120,825
Members
448,990
Latest member
rohitsomani

Share this page

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