VLOOKUP

G

Guest

Guest
Can I use vlookup with dublicate records?
ex.

ColumnA ColumnB
1 tom
2 steve
3 mike
3 joe

How can I vlookup 3 and return joe?

Any help would be great. I am new to excel.
Thank you
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi

If you only have 3 once, use:

=VLOOKUP(3,A1:B10,1,FALSE)

The False agument will mean it finds an exact match.
 
Upvote 0
Thanks Dave

But I have 3 more than once. Was wondering if there is something I could do to find the 2nd 3.

Thanks
 
Upvote 0
Ok then, as you are new to Excel I wont confuse the issue with a horribly long function, try this instead.

Lets' assume your numeric data you want to lookup in in Column A. Insert a new column at Column A so that the data is now Column B. Now in Cell A2 put this:
=IF(COUNTIF($A$1:A1,B2)=0,B2,"")

Note the absolution of $A$1
Copy this down as far as needed then use the VLOOKUP to look for the number you want in Column A eg:

=VLOOKUP(3,A1:C100,3,FALSE)
 
Upvote 0
Dave,

Again thanks for the help. Thats seems to find the 2nd one but i also need the first.
I need to be able to vlookup but 3's with the different name attached.

ex.
ColumnA ColumnB
1 tom
2 steve
3 mike
3 joe

need to vlookup 1st 3 with mike and 2nd 3 with joe.
sorry if I wasn't that clear am new at this also.

Thanks again
DaBoyz1971
 
Upvote 0
As is clear from the exchanges in this thread, you want to retrieve "joe" also when the lookup/key value is 3.

Note 1. It is better, if possible, not to associate the same key value with different values.

Assuming that A2:B5 houses your sample data:

In D1 enter: 3 [ a lookup/key value ]

In D2 enter:

=IF(COUNTIF(A:A,$D$1)>0,VLOOKUP($D$1,$A$2:$B$5,2,0),"Not Found")

In D3 enter:

=IF(COUNTIF(A:A,$D$1)>COUNTA($D$2:D2),VLOOKUP($D$1,$B$5:INDIRECT("A"&MATCH($D2,$B$1:$B$5,0)+1),2,0),"")

and copy down this till no more values are returned.

Note 2. If your data area is frequently changing (that is, the area is dynamic), a different approach might be more appropriate.


On 2002-03-09 17:06, Anonymous wrote:
Can I use vlookup with dublicate records?
ex.

ColumnA ColumnB
1 tom
2 steve
3 mike
3 joe

How can I vlookup 3 and return joe?

Any help would be great. I am new to excel.
Thank you
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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