# Vlookup

#### Andy Pilkington

##### Board Regular
I have two arrays, one with source data and the other the destination.

The source data has dates in the first column in ascending order [but not consecutive] and values in the second column.

The destination has consecutive dates in the first column and I want to show in the second column only values against the dates that appear in the source array. Vlookup function does this but it drops in values against all dates between the non-consecutive dates from the source array.

Also, my source array sometimes has two consecutive dates the same in which case the Vlookup function picks up the last of the two values only.

Is there a formula that can eliminate these glitches?

Thank you in anticipation.
ANDY PILKINGTON

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Not sure what you mean by:
but it drops in values against all dates between the non-consecutive dates

Do you mean they return NA? Are you perhaps looking to enable approximation(the last parameter of the vlookup function?)

As to the second problem, do you want to return the value of both cells that matched, or always the first as opposed to the last?

Not sure what you mean by:

Do you mean they return NA? Are you perhaps looking to enable approximation(the last parameter of the vlookup function?)

As to the second problem, do you want to return the value of both cells that matched, or always the first as opposed to the last?

#1 - My "Source" may look like this ...

Col A Col B
1 January 2014 100.00
10 January 014 200.00
15 January 2014 250.00

My destination will have all dates from 1-5 January 2014 lied. The Vlookup function returns 100.00 against all dates from 1-9 January and 200.00 from 10-14 January whereas I only want values showing against the matching date only.

#2 - My "Source" may look like this ...

Col A Col B
1 January 2014 100.00
1 January 2014 150.00

My destination will only pick up one value because my destination is a consecutive list with no duplications and it picks up the second value [150.00].

I hope this explains better?

Thanks.
ANDY

So for your first issue turn enable approximation off and then use a conditional to hide errors:
=iferror(vlookup(value,LookupRange,False),"")

For the second issue. My understanding is you want to return all values. That can't be addressed with a vlookup.

The only thing that comes to mind is to make an array formula. But I'm unsure how you'd get an array formula to display its contents in one cell(without vba). To my knowledge, you would need a cell for each "extra result".
Or you would need a vba formula to concatinate the result array.

Last edited:
Thank you. I think I have solved my problem by using SUMIF.

Thanks again,
ANDY

I think I have solved the issue with SUMIF.

Thanks again.
ANDY

Ok yea if you want to sum them then SumIf will work. I thought you wanted to return the values seperately. Glad you figured everything out.

Replies
3
Views
163
Replies
18
Views
197
Replies
4
Views
73
Replies
18
Views
426
Replies
3
Views
210

1,196,516
Messages
6,015,677
Members
441,915
Latest member
sm Hussaini

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

### Which adblocker are you using?

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

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