• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup

spmurphy_no

New Member
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,)
 
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
 
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
 
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
 
Back
Top