Need help for Offset formula

[ThrottleWorks]

Need help for Offset formula


I have following data.


Column B, names.

Column C, amounts.


Range M3 is a validated cell (list).

All the names from column B are in the validation list.


I want to write a formula in cell N3.

The formula should populate values from the column C.


For example, Cell B2 value is sachin

Cell C2 value is 100.


User has selected Sachin in the list (cell M3).

Now the formula in cell N3 should populate value as 100.


I am trying combination of vlookup and offset in cell N3.


But I am not able do it, can anyone help me in this please.

 

[Hui]

=index(c2:c100,match(m3,b2:b100,0))


Or if you really want to use Offset


=offset(c1,match(m3,b2:b100,0),)

But I wouldn't use Offset as its a Volatile function

 

[DaveTurton]

why wouldn't

=VLOOKUP(M3,B:C,2,FALSE)

work?

 

[Hui]

It would if the data was correctly sorted


Thus is he main advantage of using Index/Match

 

[ThrottleWorks]

Hui Sir Dave Sir, thanks a lot for the help.


I will be using index function. I wanted to use index function only but I thought ofset will be easy.


Once again, thanks a lot, have a nice day.


Thanks Sir, index is working.

 

[NARAYANK991]

Hi Dave ,


You are right , there is no difference between VLOOKUP with FALSE as the 4th parameter , and INDEX/MATCH , at least in this case , except ...


The following links mention two important differences :


1. http://www.excelbanter.com/showthread.php?t=238685


2. http://www.mrexcel.com/forum/excel-questions/65051-why-vlookup-vs-index-match.html


Narayan

 

[ThrottleWorks]

Thanks for the help Sir, have a nice day.