VLOOKUP Second occurance

T

theob

New Member
Joined
Jun 12, 2002
Messages
3
I am trying to find a way to return an second answer for a
second occurance using a VLOOKUP function. (maybe it can
be done with something else?) ex. range= A1:C3,
A1 = 40CA B1 = 30R2 C1 = TRUCK
A2 = 40CB B2 = 30A1 C2 = CAR
A3 = 40CA C2 = 30B3 C3 = TRAILER

VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"

How can I get a formula like this to return "TRAILER" the
second occurance of "40CA"?

Thanks, Theo
.
 
Sort by date Sort by votes
Hi Newbie here!!!

this is what I have been looking for (at least it looks like it is) for at least a month or so now!!!

BUt i cant get it too work!!!,

what does the "z" and 40 stand for?

would i have to change these for my project?

look forward for yoour advise

Donna

Aladin Akyurek said:
See the figure.


<CENTER><TABLE borderColor=#c0c0c0 cellSpacing=0 width="60%" align=center border=1><TBODY><TR><TD bgColor=#0c266b colSpan=11><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left>Microsoft Excel - aaRetrieveMultValAssocWithKey theob.xls</TD><TD align=right>___Running: xl2000 : OS = Windows Windows 2000 </TD></TR></TBODY></TABLE></TD></TR><TR><TD bgColor=#d4d0c8 colSpan=11><TABLE height=10 width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD><TD vAlign=center align=right><FORM name=formCb7552><INPUT type=button value="Copy Formula" name=btCb8704 *******='window.clipboardData.setData("Text",document.formFb3707.sltNb8739.value);'></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD bgColor=white colSpan=11><TABLE border=0><TBODY><TR><FORM name=formFb3707><TD align=middle width="5%" bgColor=white><SELECT onchange="document.formFb3707.txbFb8093.value = document.formFb3707.sltNb8739.value" name=sltNb8739><OPTION value='=MATCH(REPT("z",40),A:A)-(CELL("Row",A1)-1)'>E2<OPTION value='=IF(LEN($F$2),VLOOKUP($F$2,OFFSET($A$1,0,0,$E$2,3),3,0),"")'>G2<OPTION value='=IF(COUNTIF(OFFSET($A$1,0,0,$E$2,1),$F$2)>COUNTA($G$2:G$2),INDEX(OFFSET($C$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),MATCH($F$2,OFFSET($A$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),0)),"")'>H2<OPTION value='=IF(COUNTIF(OFFSET($A$1,0,0,$E$2,1),$F$2)>COUNTA($G$2:H$2),INDEX(OFFSET($C$1,MATCH(H2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),MATCH($F$2,OFFSET($A$1,MATCH(H2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),0)),"")' selected>I2<OPTION value='=IF(COUNTIF(OFFSET($A$1,0,0,$E$2,1),$F$2)>COUNTA($G$2:I$2),INDEX(OFFSET($C$1,MATCH(I2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),MATCH($F$2,OFFSET($A$1,MATCH(I2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),0)),"")'>J2</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=120 value='=IF(COUNTIF(OFFSET($A$1,0,0,$E$2,1),$F$2)>COUNTA($G$2:H$2),INDEX(OFFSET($C$1,MATCH(H2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),MATCH($F$2,OFFSET($A$1,MATCH(H2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),0)),"")' name=txbFb8093></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD align=middle width="1%" bgColor=#d4d0c8>

</TD><TD align=middle bgColor=#d4d0c8><CENTER>A</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>B</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>C</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>D</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>E</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>F</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>G</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>H</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>I</CENTER></TD><TD align=middle bgColor=#d4d0c8><CENTER>J</CENTER></TD></TR><TR><TD align=middle width="1%" bgColor=#d4d0c8><CENTER>1</CENTER></TD><TD vAlign=bottom align=left bgColor=#ffffff>40CA</TD><TD vAlign=bottom align=left bgColor=#ffffff>30R2</TD><TD vAlign=bottom align=left bgColor=#ffffff>TRUCK </TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD></TR><TR><TD align=middle width="1%" bgColor=#d4d0c8><CENTER>2</CENTER></TD><TD vAlign=bottom align=left bgColor=#ffffff>40CB</TD><TD vAlign=bottom align=left bgColor=#ffffff>30A1</TD><TD vAlign=bottom align=left bgColor=#ffffff>CAR </TD><TD bgColor=#ffffff>

</TD><TD vAlign=bottom align=right bgColor=#ffcc99>5</TD><TD vAlign=bottom align=left bgColor=#c0c0c0>40CA</TD><TD vAlign=bottom align=left bgColor=#ffcc99>TRUCK </TD><TD vAlign=bottom align=left bgColor=#ffcc99>TRAILER </TD><TD vAlign=bottom align=left bgColor=#ffcc99>HELI</TD><TD vAlign=bottom align=left bgColor=#ffcc99>

</TD></TR><TR><TD align=middle width="1%" bgColor=#d4d0c8><CENTER>3</CENTER></TD><TD vAlign=bottom align=left bgColor=#ffffff>40CA</TD><TD vAlign=bottom align=left bgColor=#ffffff>30B3</TD><TD vAlign=bottom align=left bgColor=#ffffff>TRAILER </TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD></TR><TR><TD align=middle width="1%" bgColor=#d4d0c8><CENTER>4</CENTER></TD><TD vAlign=bottom align=left bgColor=#ffffff>50CA</TD><TD vAlign=bottom align=left bgColor=#ffffff>25F5</TD><TD vAlign=bottom align=left bgColor=#ffffff>HELI</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD></TR><TR><TD align=middle width="1%" bgColor=#d4d0c8><CENTER>5</CENTER></TD><TD vAlign=bottom align=left bgColor=#ffffff>40CA</TD><TD vAlign=bottom align=left bgColor=#ffffff>20P3</TD><TD vAlign=bottom align=left bgColor=#ffffff>HELI</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD><TD bgColor=#ffffff>

</TD></TR><TR><TD colSpan=11>Sheet1</TD></TR></TBODY></TABLE>
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 above image was automatically generated by [HtmlMaker 2.14] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo
</CENTER>

The relevant formulas are:

E2:

=MATCH(REPT("z",40),A:A)-(CELL("Row",A1)-1)

F2:

The lookup value (Key) of interest.

G2:

=IF(LEN($F$2),VLOOKUP($F$2,OFFSET($A$1,0,0,$E$2,3),3,0),"")

H2:

=IF(COUNTIF(OFFSET($A$1,0,0,$E$2,1),$F$2)>COUNTA($G$2:G$2),
INDEX(OFFSET($C$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),
MATCH($F$2,OFFSET($A$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),0)),"")

which is copied up to J2.

Aladin



[SIZE=-1][ This Message was edited by: Aladin Akyurek on 2002-06-14 23:46 ][/SIZE]
Click to expand...
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Upvote 0
Icehockey44 said:
Hi Newbie here!!!

this is what I have been looking for (at least it looks like it is) for at least a month or so now!!!

BUt i cant get it too work!!!,
Click to expand...

See below for a more recent approach...

what does the "z" and 40 stand for?
Click to expand...

They are uses in the MATCH expression in order to determine the position/row of the last text value.

would i have to change these for my project?
Click to expand...

No, not really.

look forward for yoour advise

Donna
Click to expand...

Here is a more recent approach...

<TABLE style="WIDTH: 388pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=518><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4750" width=134><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 width=64>X</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 100pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=134>Look up value</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>40CA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64> </TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>40CA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>30R2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>TRUCK </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Occurrence count</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>40CB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>30A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>CAR </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Idx</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Z</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>40CA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>30B3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>TRAILER </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>30R2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>TRUCK </TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>50CA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>25F5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>HELI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>30B3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>TRAILER </TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>40CA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>20P3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>HELI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>20P3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>HELI</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>50CA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>25G5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>CAR </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>55CA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>40Y4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TGV</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD></TR></TBODY></TABLE>

A1:C8 houses the sample already used in this thred, which is a bit extended with some records and headers.

F1: 40CA (a look up value of interest)

F2, just enter:
Code: 
=COUNTIF(A2:A8,F1)
This determines the number of times F1 occurs.

E4, control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS($E$4:E4)<=$F$2,SMALL(IF($A$2:$A$8=$F$1,
   ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($E$4:E4)),"")

F4, just enter, copy across, and down:
Code: 
=IF(N($E4),INDEX($A$2:$C$8,$E4,MATCH(F$3,$A$1:$C$1,0)),"")
 
Upvote 0
thanks for your replies John and Aladin,

I have now managed to sort out my issues the following way:

using a unique formula with a count (my data consists of various codes with different dates) so code/date/ and the number of times they match,, then do a vlookup against this code, whcih has worked a treat. I really can't get my head around VBA apart from the simple things like recording things I do repetitively!!

The next problem is that I am trying to automate th eworkbook, and trying to do the vlookup from the unique which still has the forumla in, just isn't playing game at the moment!!

Cheers

Donna
 
Upvote 0
Icehockey44 said:
thanks for your replies John and Aladin,

I have now managed to sort out my issues the following way:

using a unique formula with a count (my data consists of various codes with different dates) so code/date/ and the number of times they match,, then do a vlookup against this code, whcih has worked a treat. I really can't get my head around VBA apart from the simple things like recording things I do repetitively!!

The next problem is that I am trying to automate th eworkbook, and trying to do the vlookup from the unique which still has the forumla in, just isn't playing game at the moment!!

Cheers

Donna
Click to expand...

Try post #13 though...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to find and spill the positions of all occurrences of a specific character in a string?
Replies
4
Views
602
Rnkhch
R
T
  • Solved
Variable Sequences
Replies
6
Views
703
Fluff
Fluff
D
  • Solved
Mapping between Multiple Cells and Text Boxes with IF Statements
Replies
6
Views
571
Denny57
D
M
  • Question
Filtering with "SUMPRODUCT()" raises "a value used in the formula is of the wrong data type"
Replies
8
Views
2K
RoryA
RoryA
L
  • Solved
Inserting new rows base on cell value, copying cell value on the first 3 column and transpose data for the rest
2
Replies
15
Views
668
Legacy 143009
L

Forum statistics

Threads
1,215,039
Messages
6,122,802
Members
449,095
Latest member
m_smith_solihull

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