Vlookup at different sheet every time

[Hessing]

Hey

I have a situation where I need to always make a Vlookup at the last sheet instead of the same sheet as started with, when i copy my data from one sheet to antoher.

I have tried to make an example in this file.

ExcelForum.xlsx

Kind regards

[ragulduy]

I guess you could replace:
Sheet1!B3
with
INDIRECT("'Sheet"&RIGHT(CELL("Filename"),1)-1&"'!B3")
for example

[martindwilson]

the formula doesnt make sense to me
=IF(B3=Sheet1!B3,VLOOKUP(B3,Sheet1!B3:K31,10,FALSE),"")
isnt that just the same as
=IF(B3=Sheet1!B3,Sheet1!K3,"") ? or am i missing something

[Hessing]

Martindwilson, you may be right that it is the same but that doesn't solve my problem?

Yudlugar I can't get your formula to work. It just show up with a #VALUE! error.
It might be because im not familiar with the INDIRECT formula.

[ragulduy]

put:
=IF(B3=Sheet1!B3,VLOOKUP(B3,INDIRECT("'Sheet"&RIGHT(CELL("Filename"),1)-1&"'!B3:K31"),10,FALSE),"")
in sheet 3 J3 and it will look at sheet2.

Note - it assumes your sheet names actually contain a number at the end.

[martindwilson]

as yudlugar says it depends on your sheet names and how many there are
RIGHT(CELL("Filename"),1)-1 will go wrong once you reach sheet 11