CODEWITHSUNDEEP
excelvlookupvba
Gautam Makesh
Gautam Makesh

Reputation: 7

VLOOKUP for multiple entries

I have a few columns in a sheet. First column being first names and the fifth being their respective age. If I want to search the age column for a particular age say '12' and return their corresponding first names in a separate sheet, what should i do? I tried VLOOKUP but I could not figure out the logic. Can someone help me out?Thank you.

Upvotes: 0

Views: 525

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

Vlookup is very easy to use, however the first column must be arranged alphabeticaly before in order for it to work properly. (and usualy i use only the exact match argument).

Upvotes: 0

Youbaraj Sharma
Youbaraj Sharma

Reputation: 1295

Unfortunately VLOOKUP will not work in this situation,as the Vlookup function cannot reference details on left side, however you can use a combination of INDEX and MATCH functions thou. Lets say you have following table

A              B
mark          11
john          23
Selly         30
Youbaraj      45

and you want to get the value of A based on the value of B, you can use something like

=INDEX(A1:A20,MATCH(1,B1:B20,0))

You can use index and match to do HLOOKUP and VLOOKUP looking into any column and getting values of any side.

You can use an Index and Double match .. to get answers from a column by entering its name.

Example:

    A      B      C      D  
   1 col1   col2   col3   col4  
   2 val1   val2   val3   val4
   3 val5   val6   val7   val8

Consider in cell C10 ColumnName: And in cell C11, you enter the name of a column

Now see what would happen with this function

 =index(A1:D3,Match(C11,A1:D1,0),Match(val-to-look-for, Column (a,b,c,d),0))

You can dynamically type a name of a column to get that column name's look up value

Upvotes: 2

Related Questions