Vlookup

[Andy Pilkington]

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

 

[ghrain22]

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?

 

[Andy Pilkington]

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

 

[ghrain22]

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.

 

[Andy Pilkington]

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

Thanks again,
ANDY

 

[Andy Pilkington]

I think I have solved the issue with SUMIF.

Thanks again.
ANDY

 

[ghrain22]

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.