Vlookup/Match?? Help please

[krosado]

I have two seperate worksheets
Worksheet one has all of my data.

In worksheet two, I want to a vlookup for a word that happens to be repeated several times, but the second column shows the difference.

ex: Worksheet 1
A1 b1
1 Dog Lab
2 Bird Parakeet
3 Dog Spaniel
4 Dog Boxer

I want a formula that if "Dog" then B1
But a list.

My vlookup currently just shows the first row and does not acknowledge rows 3 or 4 when I cut and paste the formula.

Please help!

 

[T. Valko]

I have two seperate worksheets
Worksheet one has all of my data.

In worksheet two, I want to a vlookup for a word that happens to be repeated several times, but the second column shows the difference.

ex: Worksheet 1
A1 b1
1 Dog Lab
2 Bird Parakeet
3 Dog Spaniel
4 Dog Boxer

I want a formula that if "Dog" then B1
But a list.

My vlookup currently just shows the first row and does not acknowledge rows 3 or 4 when I cut and paste the formula.

Please help!

It sounds like this is what you want to do:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[Aladin Akyurek]

Sheet1, A1:B4 houses the sample you provide...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Dog</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>Lab</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Bird</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>Parakeet</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Dog</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>Spaniel</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Dog</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>Boxer</TD></TR></TBODY></TABLE>

Sheet2

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Dog</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>List</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Lab</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Spaniel</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Boxer</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD></TR></TBODY></TABLE>

A1: Dog

A2, just enter:

=COUNTIF(Sheet1!A1:A4,A1)

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

=IF(ROWS($A$4:A4)<=$A$2,INDEX(Sheet1!$B$1:$B$4,
   SMALL(IF(Sheet1!$A$1:$A$4=$A$1,ROW(Sheet1!$B$1:$B$4)-ROW(Sheet1!$B$1)+1),
     ROWS($A$4:A4))),"")

 

[excel_vba_guru]

krosado,

Depends on it this is a task that only you need to do, or if it needs to be repeatable by many.

Here is one possible solution...

I use a custom function to perform what you are looking for.

Function Nth_Occurrence(range_look As Range, LookFor As String, FoundIt As Long, offset_row As Long, offset_col As Long)
Application.EnableCancelKey = xlDisabled


Dim Cnt As Long
Dim Result As Range

Set Result = range_look.Cells(1, 1)
For Cnt = 1 To FoundIt
Set Result = range_look.Find(LookFor, Result, xlValues, xlWhole)
Next Cnt
Nth_Occurrence = Result.Offset(offset_row, offset_col)

End Function

You will need to go into the VBA editor and create this custom function.
Please note the occurrence (FoundIt) is 0 based...Additionally, it loops thru and will find infinite number of occurrence, thus utilize the countif function to make this more dynamic.

Take a look at some examples here...

Excel VBA Guru

 

[krosado]

Correct! That is what I am looking for, but will the formula change if all data is not numerical?

I tried the formula in your example and I am not having any luck?

 

[Aladin Akyurek]

Correct! That is what I am looking for, but will the formula change if all data is not numerical?

I tried the formula in your example and I am not having any luck?

See post #3. The 2nd formula needs to be confirmed with control+shift+enter, not with just enter...

 

[T. Valko]

Correct! That is what I am looking for, but will the formula change if all data is not numerical?

I tried the formula in your example and I am not having any luck?

It doesn't matter what type of data it is.

Here's a small sample file that demonstrates this.

zzzKrosado.xls 18kb

http://cjoint.com/?AHEaLKggqFW

 

[T. Valko]

See post #3.

See post numbers 2 and 7 for a better method.

 

[krosado]

Thank you!

But it's still not working for me. In my array "Animal" I have some blanks.
Would that cause the problem? My cell that I want the "types of dogs" is saying "#value".

 

[T. Valko]

Thank you!

But it's still not working for me. In my array "Animal" I have some blanks.
Would that cause the problem? My cell that I want the "types of dogs" is saying "#value".

No, empty/blank cells won't cause a problem.

Can you post your file or at least a sample file that is representative of your real data and setup?

You can use a free file host if need be (like the one I used).