Wanting to extract specific data from a table of values

Hi,

I have attached a spreadsheet which shows data on the left hand side which is automatically download via IRESS (a software program we use) and on the right hand side (highlighted in yellow) is the data I need to extract. I have tried matches, index but it seems to bring up the wrong data and not sure what I have done wrong. I would prefer a non-macro if possible but if it's not, then happy with macro.

Hope that makes sense, let me know otherwise.

Your help would be greatly appreciated.

after you have complied to moderator request i can provide solution i worked on


ps there was no highlighting in yellow i could see....

Try these

l8
=VLOOKUP(L7,INDIRECT("C"&MATCH(L6,A:A)&":"&"H"&MATCH(L6,A:A)+25),4,0)

l9
=VLOOKUP(L7,INDIRECT("C"&MATCH(L6,A:A)&":"&"H"&MATCH(L6,A:A)+25),5,0)

l10
=VLOOKUP(L7,INDIRECT("C"&MATCH(L6,A:A)&":"&"H"&MATCH(L6,A:A)+25),6,0)

Hi

Thanks so much for the reply. I tried the above but I am not getting the correct answers. I get the price for the first month and then 0 for the last two values.

I think I see the problem. The first two columns of data is what data IRESS dumbs (dates going from today backwads running down or colmns C & D). I then transpose the data next to it with the dates running down. So effectively I am only wanting to retrieve data from Columns E to H. Hope that's not too confusing?

so you dont want to look up against date in column C but rather column E?

OK Replace "C" with "E" and update accordingly
L8
=VLOOKUP(L7,INDIRECT("E"&MATCH(L6,A:A)&":"&"H"&MATCH(L6,A:A)+25),2,0)

l9
=VLOOKUP(L7,INDIRECT("E"&MATCH(L6,A:A)&":"&"H"&MATCH(L6,A:A)+25),3,0)

L10
=VLOOKUP(L7,INDIRECT("E"&MATCH(L6,A:A)&":"&"H"&MATCH(L6,A:A)+25),4,0)

just to explain what this is doing
the indirect section is changing the array on which to lookup data based on the match formula which gives the row number of STOCK
ie, AAD = 3, ABC = 35

indirect builds the array with stock row to include the whole section which is 25 rows more than the matched row
so for AAD
array = E3:H28

once the array is set it just a regular vlookup

you would need to reassess the format of the data
as this solution only works specifically for the example which is why i explained the workings

basis of the formula is that it looks at l6 for the stock and l7 for the date
if these are else where you need to change the formula accordingly