Help need on returning data with three variables

[mboyce82]

Hi,
I need to find a way to return a variable for column J in the attached. The issues I am having is the fact that the data is dependent on the 3 variables in column A-C and I would like to return the appropiate value in column D dependent on the variables in columns G-I, interpolated or otherwise.

So far I have tried to concatenemat the variables and the do a vlookup, but this doesn't work as there are different bank names. Also I am not able to name the brange as the dfata in A-C will be dynamic and change for day to day.

Any suggestions or help will be really appreciated.

Thanks,

Michael

[tigeravatar]

mboyce82,

None of the items in G:I contain a match. For example, there is no data that has all three of these critieria:
ABBEY NATIONAL TREASURY SERVICES PLC
9/20/2012
GBP

Every row of criteria G:I is like that, there are no matches.

If there were a match, this would be the correct formula:
=INDEX($D$1:$D$39,MATCH(1,INDEX(($A$1:$A$39=G3)*($B$1:$B$39=H3)*($C$1:$C$39=I3),),0))

[canapone]

Hi

maybe:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Regards

[mboyce82]

Hi Tigeravatar,
Sorry I was not clear, I do not always expect there to be an exact match (most of the time there will not be). I these instances I would like to interpolate or perhaps find the closest date and return the corresponding value.

Thanks for you input

[tigeravatar]

mboyce82,

Attached is amodified version of your posted workbook. In cell J3 and copied down is this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter and not just enter:

Please Login or Register  to view this content.

[mboyce82]

Hi Tigeravatar,
Thank you for going to the effort of doing that.

Mike

[tigeravatar]

You're very welcome