• 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.

Replace Vlookup with exact cell

N

neeraj2050

Member
Greetings!!

I have applied Vlookup into many cells of "FILE A"which are spread across the sheet from "FILE B"

I want those Vlookup to get replaced with direct cell reference from "FILE B" i.e

using Vlookup a data is retrieved from a particular cell, so that particular cell appears into "FILE A" instead of Vlookup formula

Vlookup(a1,..............................) should replace with

=[FILE B]Sheet1!$A$1(for example)
 
You can create define name those range & update the formula once. Then all cells will be updated.

Formula -> Defined names -> Define name
 
Request you to please revisit attached file. Into that file, there is a Column marked with Desired Result. Into that column, specific cell number has been appeared.
 
You want to see to which cell vlookup formula referring instead of whole range?
i.e:
=VLOOKUP(A2,Sheet2!$A$2:$B$6,2,0)
in the highlighted area you want to see the "Sheet2!B2"
 
neeraj2050 said:
@Bosco: I want to have specific cell number i.e Sheet2!A1(It should be appeared into C2)
Click to expand...
Hi ,

What you want is quite complicated ; one such formula would be :

=INDIRECT(MID(CELL("address", INDEX(Sheet2!$B$2:$B$6, MATCH(Sheet1!A2,Sheet2!$A$2:$A$6,0))), FIND("]", CELL("address", INDEX(Sheet2!$B$2:$B$6, MATCH(Sheet1!A2,Sheet2!$A$2:$A$6,0)))) + 1, 255))

This will give the final result of 1 or 2.

If you remove the INDIRECT part of the formula , you will get the cell reference.

Narayan
 
neeraj2050 said:
@Bosco: I want to have specific cell number i.e Sheet2!A1(It should be appeared into C2)
Click to expand...
1] Removed the INDIRECT function, you can get the cell reference

="Sheet2!B"&MATCH(A2,Sheet2!A$1:A$100,0)

and, the cell will return : Sheet2!B2 or Sheet2!B3

1] Added the INDIRECT function, you can get the desired result

=INDIRECT("Sheet2!B"&MATCH(A2,Sheet2!A$1:A$100,0))

The cell will give the desired result of 1 or 2.

Regards
Bosco
 
You must log in or register to reply here.
Back
Top