Lookup for Most Recent Value of a category

  • Thread starter Thread starter SanCarlosCyclist
  • Start date Start date
S

SanCarlosCyclist

Hi, I need your help with a lookup. I want to be able to automate a
cell that populated the value of a category in a column most to the
right . Below is an example:

Category: Green Blue Green Blue Green
Value: 222 444 422 77 900


I want the results to look like this:
Blue 77
Green 900

If I add new columns to the right with Blue or Green, then I want
those numbers to update the most right values.
Also is there a way to populate a cell with the value of a category
that is 2nd most to the right? Below are desired results:
Green 422
Blue 444
Please let me know if I am not being clear. As always, I VERY MUCH
APPRECIATE your help.
 
Try this...

Category in the range B1:F1
Value in the range B2:F2

A10 = Blue
A11 = Green

Enter this formula in B10 and copy down as needed:

=LOOKUP(2,1/(B$1:F$1=A10),B$2:F$2)
 
Try this...

Category in the range B1:F1
Value in the range B2:F2

A10 = Blue
A11 = Green

Enter this formula in B10 and copy down as needed:

=LOOKUP(2,1/(B$1:F$1=A10),B$2:F$2)

--
Biff
Microsoft Excel MVP









- Show quoted text -
Click to expand...

Biff, this is GREAT. Can you tell me how to amend the formula so that
I get the 2nd most occurance to the right? 4th most occurance? This is
so helpful. I hated having to update this sheet on every iteration.
 
We'll need a different formula to do that.

Array entered** :

=INDEX(2:2,LARGE(IF(1:1="blue",COLUMN(1:1)),N))

Where N = the nth instance of "blue" from right to left

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


Try this...

Category in the range B1:F1
Value in the range B2:F2

A10 = Blue
A11 = Green

Enter this formula in B10 and copy down as needed:

=LOOKUP(2,1/(B$1:F$1=A10),B$2:F$2)

--
Biff
Microsoft Excel MVP









- Show quoted text -
Click to expand...

Biff, this is GREAT. Can you tell me how to amend the formula so that
I get the 2nd most occurance to the right? 4th most occurance? This is
so helpful. I hated having to update this sheet on every iteration.
 
We'll need a different formula to do that.

Array entered** :

=INDEX(2:2,LARGE(IF(1:1="blue",COLUMN(1:1)),N))

Where N = the nth instance of "blue" from right to left

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP









Biff, this is GREAT. Can you tell me how to amend the formula so that
I get the 2nd most occurance to the right? 4th most occurance? This is
so helpful. I hated having to update this sheet on every iteration.- Hidequoted text -

- Show quoted text -
Click to expand...



Hi Biff, I could not get it to work. Let me try to be a little more
clearer of what I am trying to do:
In Row A1:A8 are the following:
Blue Blue Green Green Green Blue Blue Green

In B1:B8 are the following:
10 20 30 40 50 60 70 80

In cell A10, I want a formula that produces the 2nd blue value to the
right which would be in this case is 60
In cell A11, I want a formula that produces the 2nd green value to the
right which would be in this case is 50

I appreciate your patience. I am new to these advance lookups.
 
Try these array formulas** :

=INDEX(B1:B8,LARGE(IF(A1:A8="blue",ROW(A1:A8)),2)-MIN(ROW(A1:A8))+1)

=INDEX(B1:B8,LARGE(IF(A1:A8="green",ROW(A1:A8)),2)-MIN(ROW(A1:A8))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


We'll need a different formula to do that.

Array entered** :

=INDEX(2:2,LARGE(IF(1:1="blue",COLUMN(1:1)),N))

Where N = the nth instance of "blue" from right to left

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP









Biff, this is GREAT. Can you tell me how to amend the formula so that
I get the 2nd most occurance to the right? 4th most occurance? This is
so helpful. I hated having to update this sheet on every iteration.- Hide
quoted text -

- Show quoted text -
Click to expand...



Hi Biff, I could not get it to work. Let me try to be a little more
clearer of what I am trying to do:
In Row A1:A8 are the following:
Blue Blue Green Green Green Blue Blue Green

In B1:B8 are the following:
10 20 30 40 50 60 70 80

In cell A10, I want a formula that produces the 2nd blue value to the
right which would be in this case is 60
In cell A11, I want a formula that produces the 2nd green value to the
right which would be in this case is 50

I appreciate your patience. I am new to these advance lookups.
 
Try these array formulas** :

=INDEX(B1:B8,LARGE(IF(A1:A8="blue",ROW(A1:A8)),2)-MIN(ROW(A1:A8))+1)

=INDEX(B1:B8,LARGE(IF(A1:A8="green",ROW(A1:A8)),2)-MIN(ROW(A1:A8))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP









Hi Biff, I could not get it to work. Let me try to be a little more
clearer of what I am trying to do:
In Row A1:A8 are the following:
Blue  Blue  Green Green  Green Blue Blue Green

In B1:B8 are the following:
10  20  30  40  50  60 70 80

In cell A10, I want a formula that produces the 2nd blue value to the
right which would be in this case is 60
In cell A11, I want a formula that produces the 2nd green value to the
right which would be in this case is 50

I appreciate your patience. I am new to these advance lookups.- Hide quoted text -

- Show quoted text -
Click to expand...

Biff, I messed up.
These valvues are Row A1:H1:
Blue Blue Green Green Green Blue Blue Green
and these values are in B2:H2:
10 20 30 40 50 60 70 80

Can you adjust your formula to In cell A10 to produce the 2nd blue
value to the
right which would be in this case is 60 and in cell A11, I want a
formula that produces the 2nd green value to the
right which would be in this case is 50? I apologize for the confusion
and repetitive posts?
 
The first formula I suggested will do what you want.

Here'a small sample file that uses both versions of the formulas for data
that runs across a row.

xSanCarlosCyclist.xls 14 kb

http://cjoint.com/?mur7U2mvbb

--
Biff
Microsoft Excel MVP


Try these array formulas** :

=INDEX(B1:B8,LARGE(IF(A1:A8="blue",ROW(A1:A8)),2)-MIN(ROW(A1:A8))+1)

=INDEX(B1:B8,LARGE(IF(A1:A8="green",ROW(A1:A8)),2)-MIN(ROW(A1:A8))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP









Hi Biff, I could not get it to work. Let me try to be a little more
clearer of what I am trying to do:
In Row A1:A8 are the following:
Blue Blue Green Green Green Blue Blue Green

In B1:B8 are the following:
10 20 30 40 50 60 70 80

In cell A10, I want a formula that produces the 2nd blue value to the
right which would be in this case is 60
In cell A11, I want a formula that produces the 2nd green value to the
right which would be in this case is 50

I appreciate your patience. I am new to these advance lookups.- Hide
quoted text -

- Show quoted text -
Click to expand...

Biff, I messed up.
These valvues are Row A1:H1:
Blue Blue Green Green Green Blue Blue Green
and these values are in B2:H2:
10 20 30 40 50 60 70 80

Can you adjust your formula to In cell A10 to produce the 2nd blue
value to the
right which would be in this case is 60 and in cell A11, I want a
formula that produces the 2nd green value to the
right which would be in this case is 50? I apologize for the confusion
and repetitive posts?
 
The first formula I suggested will do what you want.

Here'a small sample file that uses both versions of the formulas for data
that runs across a row.

xSanCarlosCyclist.xls  14 kb

http://cjoint.com/?mur7U2mvbb

--
Biff
Microsoft Excel MVP










Biff, I messed up.
These valvues are Row A1:H1:
 Blue  Blue  Green Green  Green Blue Blue Green
 and these values are in  B2:H2:
10  20  30  40  50  60 70 80

Can you adjust your formula to In cell A10 to produce the 2nd blue
value to the
right which would be in this case is 60 and in cell A11, I want a
formula that produces the 2nd green value to the
right which would be in this case is 50? I apologize for the confusion
and repetitive posts?- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Biff, when I open or download the file, I get a blank spreadsheet.
Can you send it as an attachment to sancarloscyclist@gmail. com ?
 
OK, sent you the file.

--
Biff
Microsoft Excel MVP


The first formula I suggested will do what you want.

Here'a small sample file that uses both versions of the formulas for data
that runs across a row.

xSanCarlosCyclist.xls 14 kb

http://cjoint.com/?mur7U2mvbb

--
Biff
Microsoft Excel MVP










Biff, I messed up.
These valvues are Row A1:H1:
Blue Blue Green Green Green Blue Blue Green
and these values are in B2:H2:
10 20 30 40 50 60 70 80

Can you adjust your formula to In cell A10 to produce the 2nd blue
value to the
right which would be in this case is 60 and in cell A11, I want a
formula that produces the 2nd green value to the
right which would be in this case is 50? I apologize for the confusion
and repetitive posts?- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Biff, when I open or download the file, I get a blank spreadsheet.
Can you send it as an attachment to sancarloscyclist@gmail. com ?
 
Back
Top