Vlookup help for stock data with uneven date ranges

[aad84]

Dear All,

I have a sheet with some data on stocks as [Column 1] Date [Column 2] Stock Price A, [Column 3] Date [Column 4] Stock Price B ... . The date ranges for all stocks are different for e.g. stock A starts in 31.01.2020 and ends in 31-08-2021, Stock B starts in 31.12.2021 and ends in 31.05.2021 and so on. Now on one common sheet with the date range on the left most column, I wanted the data as: [Column 1] Date [Column 2] Stock Price A [Column 3] Stock Price B, [Column 4 ] Stock Price C ..

A vlookup referencing the column for stocks is giving a wrong result. Any help as to how to correctly reference the date range.

Thanks a lot in advance.

Best Regards,
Anand

 

[phuonglinhvn]

Or you sort the data on sheet 1 by date or change the funtion

 

[Deepak]

Let's see if this workes, Put in B4 and drag right/down, It's dynamic so you have to update the row 3 correctly. If u have latest xl then that also can be by formula only.

=IFERROR(INDEX(Sheet1!$A:$T,MATCH($A4,INDEX(Sheet1!$A:$T,,MATCH(B$3,Sheet1!$4:$4,0)-1),0),MATCH(B$3,Sheet1!$4:$4,0)),"")

 

[bosco_yip]

Or try this single VLOOKUP function solution.

In B4, formula copied across right and down:

=IFERROR(VLOOKUP($A4,INDIRECT("Sheet1!"&CHAR(63+COLUMN(A$1)*2)&":$T"),2,0),"")

 

[p45cal]

Another way in the attached:
On the Expected Output sheet at cell M3 there's a results table, which is a Power Query query.
Its source data is the table on Sheet1 at cell A4. Note that this table has as its first data row the headers of your data, the actual headers of this table are irrelevant (demonstrated by nonsense headers that I've entered).
There is no need for the numbers in row 1 of either sheet.
What is important though, is

• that it needs to be a proper Excel Table, named Table2
• that the first row of this table, where it shows Date is exactly that all the way across for those date columns
• that there are alternate columns starting with the first column being a date column

There can be more columns in the source data for more stocks.
If you update/change/add/delete the data in the source data, you need to refresh the result table by right-clicking somewhere in it and choosing Refresh.

As an aside, half way through the steps in the query, there's a stage where the data is in a potentially more useful format, allowing you to create, for example, pivot table(s) which will allow you to get data out in useful formats.
On the DeleteMe sheet, I've put a static data example (it's not connected to anything and can't be 'refreshed') of your data and a pivot table showing the sort of thing you can do. Just to allow you more flexibility in reporting/filtering.

 

[aad84]

Dear phuonglinhvn, Deepak, bosco_yip, p45cal

Much appreciate all your help! Thanks a lot.

Best Regards,
AD

Let's see if this workes, Put in B4 and drag right/down, It's dynamic so you have to update the row 3 correctly. If u have latest xl then that also can be by formula only.

=IFERROR(INDEX(Sheet1!$A:$T,MATCH($A4,INDEX(Sheet1!$A:$T,,MATCH(B$3,Sheet1!$4:$4,0)-1),0),MATCH(B$3,Sheet1!$4:$4,0)),"")

Thanks a lot, your help is much appreciated!!