VLookup question to match dates in Col_index_num between two workbooks

[mwhitman]

Hello all -

First post, so please bear with me.

I am trying to write a VLookup formula as part of a cash forecast I am setting up. I am looking to pull daily bank balances (kept in a separate workbook) into my forecast for an opening cash position.

I can reference the Col_Index_Num and set it to the correct column for the date I need in the bank balance workbook, and it will return the correct value, however, I would need to update the Col_index_num 365 times to match the correct column in the bank balance workbook to my cash forecast workbook. Instead, f or the Col_index_num, I'd like to set a check that if the date in row 2 of the cash forecast worksheet matches the date in row 2 of the bank balances worksheet, then return that column's output for the lookup_value.

So, something like =vlookup(<insert company name>,table array, =if(date in bank balance worksheet b2 = date in cash forecast worksheet b2, ???,???), 0). Any thoughts on how to complete that formula would be greatly appreciated!

Thanks, Mike

 

[GerryZ]

MAYBE:
=LOOKUP(2;1/(data_array=b2);data_array)
Please provide details of data layout with just few rows and colunms with the expected outcome so we can understand better?

 

[mwhitman]

Thanks Gerry...the lookup function yielded a #REF result.
Bank balance spreadsheet looks similar to this:

	Fri
Account Name	5/1/2015
Company	500.00

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

Cash forecast spreadsheet looks similar to this:

			5/1/2015
Company
	Bank Cash	Looking for above bank balance to return value here

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

What I'm trying to lookup is the date (5/1/15) in the bank balance spreadsheet to match the date in the cash forecast spreadsheet and return the bank balance for that day in the blue lettered cell above.

 

[GerryZ]

Sorry not clear what you need!