This vlookup is melting my brain.

[fishoutofwater]

I am pretty clear on easy VLOOKUP stuff. But now I want to do a more complex VLOOKUP and I can't figure it out.

Here is the problem, in cell A1 I have this:

<table x:str="" style="border-collapse: collapse; width: 417px; height: 36px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 229pt;" width="305"><tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt; width: 229pt;" height="15" width="305">Hello {PerNam_GN_100_10001} Welcome to camp!

In that embedded part in the {...} the first 3 letters refer to another sheet (titled, "PER".) But I have 30+ sheets, and the first 3 letters might refer to sheet ABC or XYZ.

If I fill cell A2 with, "VLOOKUP(PerNam_GN_100_10001,PER!K17:M32,3,0)"
I get what I want: "Fred"
But the problem is I am writing in: "PER!K17:M32" in that formula. And what I need is for excel to magically know that because what I am VlookingUp starts with a "PER" that the range selection should be "PER!...." where the ... is the range and the sheet name mathes the start of what I am looking up... Oh no! Does that make sense how I wrote it?.

In other words, I need a formula like this:

=VLOOKUP(PerName_GN_100_10001,[Hey Excel, go the sheet that matches the LEFT(PerName_GN_100_10001,3)]!K17:M32,3,0)

How do I do that part in brackets right there?

Thanks for any suggestions....


" in that formula.



</td></tr></table>

 

[RichardMGreen]

Have a look at INDIRECT. I've used it in the past to take a sheet name from text in vlookups but I can't quite remember the correct syntax for it.

 

[fishoutofwater]

I have been playing around with indirect (cool, I never knew about that) but I am still clueless about how to combine it with vlookup, any other suggestions?

Thanks...

 

[RichardMGreen]

You need to include the INDIRECT as part of the vlookup to get it to look at the right sheet. So any sheet references would be replaced by the INDIRECT formula.

As I said, I haven't used it in a while but I'll see what I can dig out.

 

[Geoff Taylor]

Let's say you had a VLOOKUP like this

=VLOOKUP(C5,Sheet2!C1:D10,2,FALSE)

If you wanted to enter the sheet name in cell C10 the formula would be

=VLOOKUP(C5,INDIRECT(C10&"!C1:D10"),2,FALSE)

Does that help?

 

[fishoutofwater]

Yes! That helped a lot! Thank you so, so much!