VLOOKUP without clicking on the sheet name (and no VBA)

[csliger931]

Hi,
I'm looking for a way to have the VLOOKUP formula scan a different sheet for values without having to click on the different sheet. For example, lets say I have a table in sheet Shoes that has a column of names in column A ("Jeff", "Stacy", "Greg") and a column of shoe sizes in Column B (12, 8.5, 10). And then lets say in sheet ANALYSIS, I want to have a VLOOKUP formula (in Cell B1) find the shoe size for Stacy (Cell A1). Typically, I would type =VLOOKUP(A1, and then I would click the Shoes sheet, and then I would highlight columns A and B, and then I would type 2,false). The final formula would be =VLOOKUP(A1,Shoes!F10:H18,2,false). But that is going to be time consuming for what I need to do. I would prefer not have to click the sheet Shoes (and instead, just reference a a cell that has my sheet name in it). Is there a way to do this? Maybe using the INDIRECT formula? I'm an idiot when it comes to Excel, so apologies if this is a dumb question or confusing. I really appreciate the help!
Thanks!
Chris

 

[Sufiyan97]

You need to give name to the range (A:B) in "Shoes" sheet
Then after you will not need to click on sheet name
For VLOOKUP table array You can just select the name which you have given to your range in particular sheet and then column index number

 

[kweaver]

Suppose you use this in B1 of ANALYSIS:

=XLOOKUP(A2,INDIRECT("'"&A1&"'!B1:B10"),INDIRECT("'"&A1&"'!A1:A10"),"")

and have the sheet name you want in A1 of ANALYSIS and the search-for value in B1 of ANALYSIS

 

[csliger931]

Suppose you use this in B1 of ANALYSIS:

=XLOOKUP(A2,INDIRECT("'"&A1&"'!B1:B10"),INDIRECT("'"&A1&"'!A1:A10"),"")

and have the sheet name you want in A1 of ANALYSIS and the search-for value in B1 of ANALYSIS

You are awesome! I was having some trouble with the Xlookup function (for some reason, Excel didn't recognize it as a formula, even when I entered it as an array w/ Ctrl+Shft+Entr), but when I changed it to a VLOOKUP (and slightly modified your formula), it worked! I really need to learn more about the Indirect function, because it seems very useful and I rarely use it. Anyways, thanks again!

 

[csliger931]

You need to give name to the range (A:B) in "Shoes" sheet
Then after you will not need to click on sheet name
For VLOOKUP table array You can just select the name which you have given to your range in particular sheet and then column index number

Thanks for the reply!

 

[kweaver]

You are awesome! I was having some trouble with the Xlookup function (for some reason, Excel didn't recognize it as a formula, even when I entered it as an array w/ Ctrl+Shft+Entr), but when I changed it to a VLOOKUP (and slightly modified your formula), it worked! I really need to learn more about the Indirect function, because it seems very useful and I rarely use it. Anyways, thanks again!

Were you using 365 or 2016?

 

[csliger931]

Were you using 365 or 2016?

2016. I guess that would explain it.