vlookup

dulitul

Board Regular
Joined
Jan 19, 2013
Messages
193
Hey,

I have a spreadsheet with duplicate values. When I perform the vlookup it returns the corresponding value only on first instance. For duplicates it returns #N/A. But I want to have all values returned + duplicates. How can I solve that?

Thanks.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I don't see how it would return #N/A for a duplicate..
If Anything it would just return the same value for both instances.

Can you provide some examples?
And for solving it, what are your expected results ?
 

dulitul

Board Regular
Joined
Jan 19, 2013
Messages
193
I have got the following results. The vlookup returns only the first instances. If it is a duplicate it returns #N/A

GBP48473.04
USD88177
GBP70099.26
EUR50028
GBP#N/A
USD#N/A
GBP#N/A
EUR#N/A
GBP#N/A

<colgroup><col><col></colgroup><tbody>
</tbody>
 

dulitul

Board Regular
Joined
Jan 19, 2013
Messages
193

ADVERTISEMENT

BobUmlas I cannot understand exactly what should I do. I dont have a pivot table as in displayed in the video.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Can you post your actual formula, and show what results you expect?
 

dulitul

Board Regular
Joined
Jan 19, 2013
Messages
193

ADVERTISEMENT

I ve got in one sheet the first data below. I need to return the values from the table after the first data. As you can see it will return the 4 instances (because there are only 4 matches) and then it will start displaying #N/A on the rest. I expect to see all corresponding values. The formula I type in the first cell is - =VLOOKUP(G2,Exchange!A2:B6,2,FALSE)

GBP
USD
GBP
EUR
GBP
USD
GBP
EUR
GBP

and in another sheet -
Exchange in USD
DKK0.3
CHF1.2
USD1
GBP1.54
EUR1.2

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
As you can see it will return the 4 instances (because there are only 4 matches) and then it will start displaying #N/A on the rest. I expect to see all corresponding values.

Sorry, but NO, I can't see...

What is the formula you're using.
 

dulitul

Board Regular
Joined
Jan 19, 2013
Messages
193
=VLOOKUP(G2,Exchange!A2:B6,2,FALSE)

This is the formula I type in the first cell and click double click on the autofill option. And it returns only four instances. The rest is #N/A

The problem is that it somehow increments the table array. It goes like that. On the 2nd cell of the vlookup value it shows:

=VLOOKUP(D3,A3:B7,2,FALSE) 2nd

=VLOOKUP(D4,A4:B8,2,FALSE) 3th

=VLOOKUP(D5,A5:B9,2,FALSE) 4th

#N/A 5th

# N/A 6th etc.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,958
Members
414,352
Latest member
macquarie_jchan58

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
Top