Return Cell reference of a vlookup value

[ssurajkr]

Hi Guys,

Please help me on the following issue

In Sheet 1, I have Name of the product in Column A and Price in Column B.

In Sheet 2, I have name of the product the customer needs. So when I do a Vlookup formula, excel returns me the value. However when i hold Control and [, excel takes me to the entire array instead of the cell itself.

Could you please help me, how do I get Cell reference of a Vlookup value, or When i hold Control [ on a Vlookup Value Cell, it needs to take me to the cell instead of the entire array. Saves a lot of my time. Now i need to press Control F for every product, which is tedious considering the number of products i have.

Thanks.

 

[mvptomlinson]

I'm pretty sure you can't change the functionality of CTRL+[ (without using an OnKey macro), but you could use another formula to tell you the address of where it found the VLOOKUP price.

If your price table is in A1:B10, and you're looking up a product listed in cell E1 using something like this in F1:

=VLOOKUP(E1,A1:B10,2,0)

Then in G1 use:

=ADDRESS(MATCH(E1,A1:B10,0),2)

 

[ssurajkr]

Wow!

That was fast.

Thanks

 

[ssurajkr]

Hey,

Can i use the cell value (Lets Say it returns $B$11) to link the vlookup value?

My ultimate objective is to link, so that Control [ works.

 

[AlphaFrog]

Thus would create a clickable link to the product match on Sheet1 in column A of the product listed in cell E1.

=HYPERLINK("[Book1.xls]"&ADDRESS(MATCH(E1,Sheet1!A:A,0),1,,,"Sheet1"),E1)

Put the name of your workbook in the formula.


EDIT: a better way I think.
=HYPERLINK(ADDRESS(MATCH(E1,Sheet1!A:A,0),1,,,"[Book1.xls]Sheet1"),E1)

 

[mvptomlinson]

I don't think you'll ever be able to use a shortcut key combo to do what you're seeking - without using an OnKey event. CTRL+[ and CTRL+] look for cell precedents and dependents; they don't act as a link to that cell.

You could adjust the formula to create a hyperlink in the cell instead of just the address. You can then click on it and it will take you to the referenced cell.

=HYPERLINK(ADDRESS(MATCH(E1,A1:A10,0),2))

 

[ssurajkr]

Hyperlink works fine. Makes my work a lot easier

Thanks guys.

Appreciate your concern to help everyone.