lookup with IF LEFT function?

[D. Nish]

Hello,

I'm new to the site although I've referred to it a number of times and find it very useful.
I'm hoping someone out there has can help me out...

On the first sheet (Release) that displays store ordering schedules. The cells that have scheduled orders may say something different, but will begin with "Release Order."
The 2nd sheet (Qty), that has daily ordering quantites for each store.
On the 3rd sheet, I am using the IF(LEFT function to look at the Release sheet, and if the cell starts with "Rel" populate the cell with the corresponding quantity from the Qty sheet.
This only works if stores and dates on the first two sheets are all aligned the same.

Is there a way to do this with a lookup by store?
I've attached an example of the data in the workbook (hopefully I did it correctly).

[azumi]

Maybe, put this on B2 on sheet "Release Day", then copied down and cross:

=IF(INDEX(Release!$B$2:$H$11,MATCH($A2,Release!$A$2:$A$11,0),MATCH(B$1,Release!$B$1:$H$1,0))="","",INDEX(Qty!$B$2:$H$11,MATCH($A2,Qty!$A$2:$A$11,0),MATCH(B$1,Qty!$B$1:$H$1,0)))

[D. Nish]

Thanks azumi! This is exactly what I was looking for.

[D. Nish]

After playing with this a bit, I realize it's not exactly what I was looking for. The formula provided by Azumi pulls over the data from the 2nd sheet if there is anything in the cell on the 1st sheet. I originally used the 'left' function because there can be anything in the cells on the first sheet, and I only want the data if what's in the cell starts with "Rel". I've attached the same spreadsheet with other data in the cells of the 1st sheet.

[JeteMc]

This proposal employs two helper tables which may be moved and/or hidden for aesthetic purposes.
Notice that the store numbers and dates have been rearranged on the Release and Qty sheets so that the store numbers of the two tables are not aligned.
The first helper table (B20:H29) is populated using: =INDEX(Release!$B$2:$H$11,MATCH($A20,Release!$A$2:$A$11,0),MATCH(B$19,Release!$B$1:$H$1,0))
The second helper table (K20:Q29) is populated using: =INDEX(Qty!$B$2:$H$11,MATCH($A20,Qty!$A$2:$A$11,0),MATCH(K$19,Qty!$B$1:$H$1,0))
The final table (red font) is populated using: =IF(LEFT(B20,3)="Rel",K20,"")
Let us know if you have any questions.

[D. Nish]

Hi Jete, thanks for your response!

I actually figured it out (by adapting Azumi's formula), but forgot to post the solution.

Here it is if anyone's interested:
=IF(LEFT(INDEX(Release!$B$2:$H$11,MATCH($A2,Release!$A$2:$A$11,0),MATCH(B$1,Release!$B$1:$H$1,0)),3)="Rel",INDEX(Qty!$B$2:$H$11,MATCH($A2,Qty!$A$2:$A$11,0),MATCH(B$1,Qty!$B$1:$H$1,0)),"")

[JeteMc]

Glad that you got a solution. Please take a moment to mark the thread as 'Solved' using the thread tools drop down above your first post. I hope that you have a blessed day.