VLOOKUP with LEFT & INDIRECT Function

[troop2118]

I am again struggling with the LEFT function, this time in combination with VLOOKUP.

The following function works well:


=VLOOKUP(D8,(INDIRECT(F$5&"!$G$9:$M$3000")),7,FALSE)

This is the number that is looked up in the sheet that is indirectly referred to and of course in the cell D8.
03-005-1-00

What I would like to do now is to put only 03-005 in D8 and have that value looked up in the indirectly linked sheet. In my understanding it should look like this:


=VLOOKUP(D8,(LEFT(INDIRECT(F$5&"!$G$9:$M$3000"),6)),7,FALSE)

However, it doesn;t work. Any idea about my mistake?

thanks

[JBeaucaire]

1) What is in D8?
2) What is in F5?
3) What is the desired sheetname that would be found as a result?

[troop2118]

1) What is in D8?
2) What is in F5?
3) What is the desired sheetname that would be found as a result?

1.) currently "03-005-1-00" which works perfectly well with the formula I wrote first. But should be replaced by 03-005 only.
2.) "MasterList" is in F5.
3.) The sheet name is also MasterList

In "MasterList" Column G has ID Numbers like "03-005-1-00" and corresponding prices in Column M.

In my sheet with the formula I would like to have only the first 6 Characters in Column D. e.g. D4=03-005 which would lookup the price for "03-005-1-00" in Masterlist.

[JBeaucaire]

Maybe:

=VLOOKUP(D8&"*",(INDIRECT(F$5&"!$G$9:$M$3000")),7,FALSE)


...or if it that specific:

=VLOOKUP(D8&"-1-00",(INDIRECT(F$5&"!$G$9:$M$3000")),7,FALSE)

[troop2118]

Thanks a lot. First one works perfect. 2nd solution is not suitable since I will not have the -1-00 in "D" column. Thanks again - problem solved.