Vlookup/formula question

[bzblewski]

I am using the vlookup function accross two tabs. . . we'll call them TAB1 and TAB2. On TAB1 I have all my base information which I am referencing from, TAB2 is the sheet where i am using the vlookup formula to extract the information i need from TAB1.

Using cell B2 as an example from TAB2:
I used a vlookup formula in cell B2 and got the information from TAB1. The vlookup formula pulled, correctly, cell F17 from TAB1.

Here is the issue:
Instead of the formula reading "=vlookup(...)". I would like the cell to simply read =F17.

Any ideas on how i can do this easily? I am doing this for alot of cells. . .

 

[Gerald Higgins]

I can't imagine why you'd want to do this. Can you explain please ?

 

[PaddyD]

"I would like the cell to simply read =F17."

Why?

 

[bzblewski]

my manager wants it to read =F17 as it is more simple than deciphering a vlookup function. he can then determine if it is ultimately the right information and can compare the spreadsheets more easily.

I know it sounds silly, but this is my life

 

[KWMSeattle]

I don't understand either. If you don't want to use VLOOKUP, just go ahead and manually link to the cells. Then you'll see "=F17" in the cells. Sounds like a lot of unnecessary work though...

EDIT: Just read your post above. You can try adding a formula next to your VLOOKUP cell to provide the reference. Something like this:

On TAB2, next to your VLOOKUP cell try:

="B"&MATCH(A1,Sheet1!A:A,FALSE)

Where A1 is your lookup value and "B" is the column containing whatever data you are pulling by looking up A1.

 

[Gerald Higgins]

Let me get this straight - so B1 could contain =F17, and B2 could contain =F6, and B3 could contain =F301, for example.

Have I got this right ?

This seems crazy.
I think your time would be better spent explaining to your boss how VLOOKUP works

I think it would be possible to write a formula that does this, but it seems like a complete waste of time to me.

 

[bzblewski]

long story short, i can tell excel which cells i would like to link A WHOLE LOT FASTER using vlookup than just linking the individual cells. But ultimately, by the higher ups i work with, the cell needs to read, simply
"=cell"

from what i am getting, this doesn't sound possible. . . .

 

[Fazza]

Wow!

Yes it could be done. But, surely you'd never do it? Just image what happens when either the value being looked up or the lookup table data changes!

Suggest you tell your manager you can't do it. And maybe offer instead to put in another column that returns just the row number in the looked up table. Say using a MATCH formula.

Good luck. Regards, Fazza

 

[PaddyD]

& boss responds with something to the effec "why not, I'm the most important person round here" point them towards this thread. The closest I'd go to this would be, as already suggested, have the vlookup return the result in one cell & next to it return the address:

If your vlookup was:

=VLOOKUP(C1,A1:B4,2,0)

then:

=ADDRESS(MATCH(C1,A1:A4,0),1)

 

[PaddyD]

" i can tell excel which cells i would like to link "

Also - you don't need to link to a cell - that's what the vlookup()'s doing for you?!?