2nd Reference

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

2nd Reference

Post by JoeExcelHelp »

I have the attached that references a specific sheet and with a VLookup reference and displays the corresponding value connected to column 7. Can you assist in modifying the formula to combine an HLookup reference within the same sheet rather then 7?.. thanks
=VLOOKUP($A$1,INDIRECT("'"&B11&"'!$A$1:T312"),7,0)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: 2nd Reference

Post by HansV »

Which cell would you want to look up? Let's say it is B1, and you want to look it up in B1:T1 in the other sheet.

=INDEX(INDIRECT("'"&B11&"'!$B$2:$T$312"),MATCH($A$1,INDIRECT("'"&B11&"'!$A$2:$A$312"),0),MATCH($B$1,INDIRECT("'"&B11&"'!$B$1:$T$1"),0))
Best wishes,
Hans

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: 2nd Reference

Post by JoeExcelHelp »

I would need to reference 2 values H and V.. I don't know how to incorporate the H without changing the indirect.. I need the formula to use the indirect as it automatically references a sheet name.. Please Let me know if you better info.. For the purposes of this formula you could reference any cell, column or row.. I'll just adjust it

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: 2nd Reference

Post by HansV »

Please try to explain as clearly as you can what exactly you want to accomplish. A screenshot and/or sample workbook might help.
Best wishes,
Hans

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: 2nd Reference

Post by JoeExcelHelp »

Sorry about the confusion.. let me try and explain again
If on sheet1 i have a range of A1:N10
A1:A10 has values 1,2,3,4..14
A1:N1 has values A,B,C,D..N
for example, within my formula if i select (2) and (B), both are variable, I need a formula that retrieves B2 and automatically references (Sheet1) as in the formula above.. thanks again

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: 2nd Reference

Post by Rudi »

Would this sample workbook provide some guidance?
Two Way Lookup.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: 2nd Reference

Post by JoeExcelHelp »

Yes.. perfect thank you