# vlookup

#### dulitul

##### Board Regular
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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 ?

I have got the following results. The vlookup returns only the first instances. If it is a duplicate it returns #N/A

 GBP 48473.04 USD 88177 GBP 70099.26 EUR 50028 GBP #N/A USD #N/A GBP #N/A EUR #N/A GBP #N/A

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

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

Can you post your actual formula, and show what results you expect?

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 DKK 0.3 CHF 1.2 USD 1 GBP 1.54 EUR 1.2

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:
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.

=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:
Try

=VLOOKUP(G2,Exchange!A\$2:B\$6,2,FALSE)

Replies
4
Views
162
Replies
6
Views
105
Replies
16
Views
436
Replies
1
Views
21
Replies
3
Views
59

1,207,280
Messages
6,077,506
Members
446,287
Latest member
tjverdugo85

### 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?

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