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:
[TABLE="width: 583"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]Account Name[/TD]
[TD]5/1/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]500.00[/TD]
[/TR]
</tbody>[/TABLE]

Cash forecast spreadsheet looks similar to this:
[TABLE="width: 205"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5/1/2015[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Company[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Bank Cash[/TD]
[TD="align: right"]Looking for above bank balance to return value here[/TD]
[/TR]
</tbody>[/TABLE]


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!