How To Get VLOOKUP Over Multiple Columns

[michaelkdown]

I am hoping someone will be able to help, i am trying to get a VLOOKUP to work over a number of different columns. An example the problem:

Sheet1

A B

1 10
2 20
3 30
4 40
5 50

Sheet1 is the sheet i want to lookup the values and i want to place the results into column B

Sheet2

A B C
1 10 40 50
2 20
3 30

Sheet2 contains all the infromation that i want to look for to make the comparison.

I know that the expression used for the VLOOKUP is:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The issue i have is "col_index_num" as i want to look over multipule columns. I have been using this expression:

=VLOOKUP(A2,Sheet2!$A$1:$J$20000,1,FALSE)

How do i change the "col_index_num" to look over all 3 columns and not just the 1st, 2nd, or 3rd??

Many Thanks

 

[Sthrncali]

I apologize but I am a little perplexed as to what you are asking...

How do i change the "col_index_num" to look over all 3 columns and not just the 1st, 2nd, or 3rd??

The "Col_Index_Num" parameter is referring to which column, relative to your match, you want to return the value from..

i.e., if your match was found on Row5, ColA, and your Col_Index_Num parameter was set to 2, the formula would return the value in Row5, ColB.

If you are wanting to return Col's B:D into a single cell - you could use a Concatenate to combine multiple Vlookups...

i.e.

=Concatenate(Vlookup1, Vlookup2, Vlookup3)

If that is not what your looking for, please provide more detail as to exactly what your trying to accomplish... It would be best to provide sample data, and then spell out what the result of the formula would be given your sample data provided...

 

[michaelkdown]

Hello,

Thank you for your reply. To expand on my initial question, I have created a simple spread sheet that explains the issue that I am having a little more clearly.

If you go to this Google Doc Spreadsheet you will see the scenario that i am talking about.

https://spreadsheets.google.com/spreadsheet/ccc?key=0AmXUHDmGHaUxdHJUeWRHWEZldnNhYmZGUmFrazk4amc&hl=en_GB


You will see in the doc that i am trying to look over three columns on Sheet2 but i only want to write one function that will do so.

Does this make more sense??

Many Thanks

 

[relianceaaa]

Hello,



https://spreadsheets.google.com/spr...GHaUxdHJUeWRHWEZldnNhYmZGUmFrazk4amc&hl=en_GB


You will see in the doc that i am trying to look over three columns on Sheet2 but i only want to write one function that will do so.

Does this make more sense??

Many Thanks

could you please help me with vlookup formula for finding
identifying non common names in excel

https://docs.google.com/spreadsheet/ccc?key=0Aj-_rtgqFgTvdGlmQUlobld6YVd6Rzk2WkxtTFFJc0E&usp=sharing

 

[taurean]

Rest of the description received via PM

For Example I am having 2 excel sheets
EXCEL SHEET 1
Name age
AA 1
BB 2
CC 3
DD 4
EE 5

EXCEL SHEET 2
TABLE 2
Name Age
BB 4
CC 9
XX 7
YY 8
ZZ 5

I want to remove unique names and list repeating names in an order.
Result should be like

EXCEL SHEET 3
Name Age Name Age
BB 2 BB 4
CC 3 CC 9


In excel sheet 1 and 2... BB and CC alone common.
others should automatically get deleted.
Coule you please help me how to sort out this problem

Regards
Ram

Here is one formula based approach based on your description [On Sheet1 and Sheet2 data is placed in range A1:B10]. The first formula needs to be committed using Control + Shift + Enter key together.

Sheet3

	A	B	C

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #808080"]Name[/TD]
[TD="bgcolor: #808080"]Salary 1[/TD]
[TD="bgcolor: #808080"]Salary 2[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ffff00"]AA[/TD]
[TD="bgcolor: #ffffcc, align: right"]11[/TD]
[TD="bgcolor: #ffffcc, align: right"]33[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ffff00"]BB[/TD]
[TD="bgcolor: #ffffcc, align: right"]44[/TD]
[TD="bgcolor: #ffffcc, align: right"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffff00"]CC[/TD]
[TD="bgcolor: #ffffcc, align: right"]33[/TD]
[TD="bgcolor: #ffffcc, align: right"]55[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 {=INDEX(Sheet1!$A$1:$A$10,SMALL(IF(ISNUMBER(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$2:$A$10,0)),ROW(Sheet1!$A$1:$A$10)),ROWS($A$1:A1)))} B2 =VLOOKUP($A2,Sheet1!$A$1:$B$10,2,0) C2 =VLOOKUP($A2,Sheet2!$A$1:$B$10,2,0) <tbody> </tbody>

<tbody>
</tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4 ..

 

[relianceaaa]

thank you sir
Ram