vlookup

[spmurphy_no]

I am trying to get vlookup to read any work sheet that is open (only active work book) and has "Sheet" as the tab name. I came up with this . can someone get this to work?

=vlookup(B2,indirect("sheet"&""),!E:E,1,)

 

[ETAF]

why the space - and why indirect - its not doing anything

what is it you want to do?

indirect - usually referes to a cell to get a value from and use in a formula

you also have a , at end without true or false

=vlookup(B2,indirect("sheet"&""),!E:E,1,)

and commas between the sheet and the range with a ! which will not work

and sample spreadsheet with what you are trying to do would help a lot here

also the version of excel you are using may help

 

[spmurphy_no]

I an using office 365. i will have a work book with multiple work sheets called "sheet1, sheet2 ,...etc". I need a vlookup that can look in cell "B2" (current sheet where the formula is written)search all sheets column E and return a the value that matches cell E

 

[ETAF]

how many sheets - you could use a nested IFERROR
=IFERROR(VLOOKUP(B2,Sheet1!E:E,1,FALSE),IFERROR(VLOOKUP(B2,Sheet2!E:E,1,FALSE),IFERROR(VLOOKUP(B2,Sheet3!E:E,1,FALSE),"not found")))

That will return want is in B2 , as you are looking in column E for the value in B2 and then returning that value - if not found then an error

 

[spmurphy_no]

the sheet can very. one week i can have three, and another could be fifty