Automating VLookups

Probably dont need VBA for this, INDIRECT will probably do what you want, seeing as you have a list of the sheet names

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

I presume your sheet names are not as simple as 1, 2, 3 etc?

OK, try this, copied down...
=IFERROR(INDEX(INDIRECT("'"&soldList!$J2&"'!$B$1:$B$100"),MATCH(L$1,INDIRECT("'"&soldList!$J2&"'!$A$1:$A$100"),0)),"")

It should have given an error message I forgot to tell you to add a : to the end of L1 "Buyer Name", so it matches the data on the sheets.

Did you put that in *** Data L2, copied down?

LOL the forum server replaced the abbreviation for Cumulative, with ***

ANyway, Im happy that worked for you. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

Nuccio92,

If you must cross-post, please provide a link to the other forum as I have done with with this link. Failure to do so is in breach of Rule 8 (refer here) of our forum rule Don't cross-post without a link.

Robert

=IFERROR(INDEX(INDIRECT("'"&soldList!$J2&"'!$B$1:$B$100"),MATCH(L$1,INDIRECT("'"&soldList!$J2&"'!$A$1:$A$100"),0)),"")

It is a *basic* INDEX/MATCH, really, but I added INDIRECT() to cater for the sheet names being variable.

If you are not familiar with INDEX/MATCH, let me know and I will explain that part.

Excel cannot normally use text as a reference in a formula (apart from range names), so we use INDIRECT to convert that text into something excel can use. I have used this twice in the formula, just changed the range it applies to. Both work the same, so I will explain the 1st 1

1. =INDIRECT("'"&soldList!$J2&"'!$B$1:$B$100")
excel adds ' at the start and end of a sheet name, so thats what we need to put in there 1st, but ' on its own is text, so you will see that I wrapped it in "" (as for all text, used in any formula)

2. =INDIRECT("'"&soldList!$J2&"'!$B$1:$B$100")
You join (concatenate) things in excel by using &, si I am using that to combine ' with the sheet name
I get the sheet name from the SoldList sheet, in column J2, downwards - in this case, SoldList!J2 contains 1 (sheet name 1)

3. =INDIRECT("'"&soldList!$J2&"'!$B$1:$B$100")
The last part is the range that is being used. This is what you would need to adjust is your range is longer than 100 lines on each of your sheets. If you have some sheets longer than others, you dont need to adjust it for each sheet, just make it cater for the longest sheet.
Again, it starts with & to show we want to combine something. Then the " for text and then '! (to finish off the sheet name), followed by the range

Hope that made sense? If not, shout again

(thanks for the feedback, too)

The IFERROR() is there to catch/trap error answers. If the MATCH in INDEX/MATCH (explained below) does not find a match, it gives an error message. IFERROR replaces that message what something of your choosing. The syntax is...
=IFERROR(formula,what-to-show-if-error-is-found)
So, if your formula works OK, its result is shown, otherwise whatever you put in the "what-to=show" part, is displayed. This could be nothing "" (like I used), it could be a message "no data found", or it could even be another formula.

INDEX returns a value at the intersect (meeting) of a row and a column.
=INDEX(range,row-num,column-num)
MATCH finds a specified criteria in a column or row.
=MATCH(Criteria-to-find,column-(or row)-to-search,0) The 0 returns an exact match

So when you put them together, you have a very powerful tool for returning a value from an unknown intersection of rows and columns.
INDEX/MATCH (or INDEX/MATCH/MATCH if you need to return data from different columns), lets you determine that intersect (meeting).

To keep this simple, I will use the same formula, but base it off ONLY sheet 1 (the INDIRECT() bit just muddles up the explanation, and we have already been through that)

=INDEX('1'!$B$1:$B$30,MATCH("Buyer Name:",'1'!$A$1:$A$30,0))
This is what that formula would look like in it's simplest form

I then used a cell reference, so that the hard-coded name can change...
=INDEX('1'!$B$1:$B$30,MATCH(L$1,'1'!$A$1:$A$30,0))

What this is doing, is saying (you generally read formulas from the inside, outwards)...
Find "Buyer Name:" in sheet 1, in range A1:A30, and find an exact match (the $ is there to "fix" the range so it doesnt adjust when copied)
OR
Find the contents of L1 in sheet 1, in range A1:A30, and find an exact match
This returns row 12, in this case

This will give you a row number. INDEX uses that to return a value. (we did not use the 2nd MATCH because we already know the column that contains out info - A)
So INDEX will go down its range, and look for the contents in the 12th row

To see how all this is working, click on the formula, then click on the Fx button to the left of the formula bar, it will also help you understand what different parts of the formula is doing, and will help you lear/underatand
=IFERROR(INDEX(INDIRECT("'"&soldList!$J2&"'!$B$1:$B$100"),MATCH(L$1,INDIRECT("'"&soldList!$J2&"'!$A$1:$A$100"),0)),"")