HOW TO USE LOOKUP

[lookup]

suppose on one sheet in col.A there are names,and againt them o.e in clumn B
there are values,if we want to pickup the values against names we use
vlookup, but what to do if the same is reversed, that means the values are in
cloumn B and names are in column A

 

[Ron Coderre]

Typically, this kind of approach would be used:

C1: (a value to find in Col_B)

This formula returns the Name in Col_A that corresponds to the
value in Col_B:
D1: =INDEX(A1:A100,MATCH(C1,B1:B100,0))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ken]

Ron ... I am jumping in here with an additional question?

If Col B contains repeat values ... How do you adjust "index/match" formula
to return proper "Name" from Col A? ... Thanks ... Kha

 

[Ron Coderre]

Hi, Kha

If the only criteria you have is a value (that may be repeated),
how would you know which name to return?

There would need to be additional criteria, like:
.. Match the last name listed.
.. Match the first name listed.
etc

I hope that helps.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[lookup]

DEAR RON,

THANKS FOR THE HELP,

ON MORE QUESTION . AS ASKED BY KEN, WHAT WE CAN DO IF THERE ARE REPEATING
NAMES IN COL B.
THE SAME PROBLEMS ARISES WITH VLOOKUP WHEN THERE ARE REPEATING NAMES, IT
PICK UP VALUES ONLY OF FIRST NAME WHICH HAS BEEN REPEATED A NO OF TIMES.

THANKS
DEEPAK

 

[Ron Coderre]

With A1:B9 containing this list
1 Alpha
2 Bravo
3 Charlie
1 Delta
5 Echo
1 Foxtrot
1 Golf
2 Hotel
3 India

Try this:
D1: (the Col_A item to match)

Put this ARRAY FORMULA in
E1: =IF(ROW()>COUNTIF($A$1:$A$10,$D$1),"",
INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$D$1,
ROW($A$1:$A$10)),ROW())))

Note: Commit Array Formulas with Ctrl+Shift+Enter,
instead of just pressing Enter.

Copy E1 and paste into E2 and down as far as you need.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)