Left Function Nested in Array on Vlookup

[scohn80215]

I'm trying to do a simple vlookup, but I need the array to contain a left formula to exclude the last two digits.

For example,

=VLOOKUP(B2,LEFT('[Daily postings (002).xlsx]GL Details Report'!$B$26,9),1,FALSE)

but I'm getting a #value !.

Thank you,

 

[philwojo]

You can't use the left formula (I don't believe) as your table array. Plus you don't fully define a table array in that formula, you only specify $B$26, so that is only one cell. But then you are trying to look at the 9th column, so that won't work. Your table array needs to be something like $B$29:$J$50 or something to that affect for the range of values you want to look in.

 

[Jonmo1]

Wait, that doesn't make sense. Your lookup range is just a single cell.
So for that, I would do
=IF(LEFT(cell,9)=B2,B2,"")

What would your plain vlookup look like if you didn't have to be concerned with only the left9 ?

 

[scohn80215]

1) Your array can consist of a single cell.
2) It's an example. Calm down.

Wait, that doesn't make sense. Your lookup range is just a single cell.
So for that, I would do
=IF(LEFT(cell,9)=B2,B2,"")

What would your plain vlookup look like if you didn't have to be concerned with only the left9 ?

 

[philwojo]

OK, so you are asking for help, but now you aren't giving us any feedback. Your array can consist of a single cell, but then it isn't an array and your vlookup won't work.

So, do you care to provide more information so we can try and help you?

 

[Jonmo1]

2) It's an example. Calm down.

Not a very good one.

But you can't use the LEFT function on the lookup range.

Taking your example literally, try
=VLOOKUP(B2&"*",'[Daily postings (002).xlsx]GL Details Report'!$B$26,1,FALSE)