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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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 ?
 
Upvote 0
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>
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
=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:
Upvote 0

Forum statistics

Threads
1,213,700
Messages
6,115,274
Members
448,627
Latest member
gc2001

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