Vlookup on second instance of lookup value

[Ziggy12]

I have a list of lookup values where infrequently the same value appears in the Lookup value list that needs to return the second lookup value in the look up range.

e.g.
Lkup Value........LkUpRange
cat..................elephant
dog..................eel........Z
cat...................cat......X
rat....................rat......AA
........................cat....CC
.........................dog....VV

I need the first instance of cat to return X while the second returns CC
The lists are of variable length and the duplicates in the lkup value or lkup range could be a different number of rows apart but the second LkUpValue instance should always return the second instance in the LkUpRange.

 

[narendra1302]

Hi,

Any update ?

 

[JeromeTurner]

Hi T. Valko - can you help me with a similar question?

I have list A
1....q
4....a
1....r
1....s
7....w
1....t
4....b

And List B
1a
1b
1c
1d
4a
4b
7a

For each of the #'s on list B, I want to return the value from list A in their original order to get
1a....q
1b....r
1c....s
1d....t
4a....a
4b....b
7a....w

Try this...

Loolup table

Sheet1

■	E	F
elephant	O
eel	Z
cat	X
rat	AA
cat	CC
dog	VV

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

</tbody>

Data area:

Sheet1

■	A	B
cat	X
dog	VV
cat	CC
rat	AA

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

This array formula** entered in B2 and copied down:

=INDEX(F:F,SMALL(IF(E$2:E$7=A2,ROW(E$2:E$7)),COUNTIF(A$2:A2,A2)))

** 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.

 

[Aladin Akyurek]

Hi T. Valko - can you help me with a similar question?

I have list A
1....q
4....a
1....r
1....s
7....w
1....t
4....b

And List B
1a
1b
1c
1d
4a
4b
7a

For each of the #'s on list B, I want to return the value from list A in their original order to get
1a....q
1b....r
1c....s
1d....t
4a....a
4b....b
7a....w

[TABLE="width: 240"]
<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent"]q[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]1a[/TD]
[TD="class: xl64, bgcolor: transparent"]q[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]4[/TD]
[TD="class: xl64, bgcolor: transparent"]a[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]1b[/TD]
[TD="class: xl64, bgcolor: transparent"]r[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent"]r[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]1c[/TD]
[TD="class: xl64, bgcolor: transparent"]s[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent"]s[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]1d[/TD]
[TD="class: xl64, bgcolor: transparent"]t[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]7[/TD]
[TD="class: xl64, bgcolor: transparent"]w[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]4a[/TD]
[TD="class: xl64, bgcolor: transparent"]a[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent"]t[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]4b[/TD]
[TD="class: xl64, bgcolor: transparent"]b[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]4[/TD]
[TD="class: xl64, bgcolor: transparent"]b[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]7a[/TD]
[TD="class: xl64, bgcolor: transparent"]w[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

E2, control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=LEFT(D2)+0,ROW($B$2:$B$8)-ROW($B$2)+1),
  COUNTIF($D$2:D2,LEFT(D2)&"?")))

 

[JeromeTurner]

Thanks for your reply Aladin!
It seems to work on your sheet but not mine
Is there a difference made if there are some entries which do not match?
For example:

the result is more like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD]1a[/TD]
[TD]r[/TD]
[/TR]
[TR]
[TD]1b[/TD]
[TD]s[/TD]
[/TR]
[TR]
[TD]1d[/TD]
[TD]t[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2a[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]4a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]5a[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]5b[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]w[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Thanks for your reply Aladin!
It seems to work on your sheet but not mine
Is there a difference made if there are some entries which do not match?
For example:

the result is more like this

[TABLE="width: 500"]
<TBODY>[TR]
[TD]1
[/TD]
[TD]q
[/TD]
[/TR]
[TR]
[TD]1a
[/TD]
[TD]r
[/TD]
[/TR]
[TR]
[TD]1b
[/TD]
[TD]s
[/TD]
[/TR]
[TR]
[TD]1d
[/TD]
[TD]t
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]2a
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]4a
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]5a
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]5b
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]w
[/TD]
[/TR]
</TBODY>[/TABLE]

Hopefully you don't have any other surprises. Admittedly, it is a nice challenge...

[TABLE="width: 242"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2389" width=67><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]q[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]q[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"]a[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]1a[/TD]
[TD="class: xl66, bgcolor: transparent"]r[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]r[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]1b[/TD]
[TD="class: xl66, bgcolor: transparent"]s[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]s[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]1d[/TD]
[TD="class: xl66, bgcolor: transparent"]t[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]7[/TD]
[TD="class: xl66, bgcolor: transparent"]w[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]t[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]2a[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]4[/TD]
[TD="class: xl66, bgcolor: transparent"]b[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl66, bgcolor: transparent"]a[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]4a[/TD]
[TD="class: xl66, bgcolor: transparent"]b[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]5[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]5a[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]5b[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]6[/TD]
[TD="class: xl66, bgcolor: transparent"]-[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 64, bgcolor: white"]7[/TD]
[TD="class: xl66, bgcolor: transparent"]w[/TD]
[/TR]
</TBODY>[/TABLE]

E2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=LEFT(D2&"#")+0,
  ROW($B$2:$B$8)-ROW($B$2)+1),
  SUM(ISNUMBER(SEARCH(LEFT($D$2:D2),D2))+0))),"-")