Home » Forums » Open Source Learning Center » Question and Answer

Auto fill a column based on three columns

Submitted by harish on 16 November, 2012 - 08:35

Hi All,

I want to auto fill a column value based on values of three cells.Currently i m using vlookup for this but it is taking only left most column.So, is there any other way to do this.

for example

product name,version,size,rate

rate column should auto fill after selecting product,version and size using drop downs.

I have table in other sheet containing all these details.

‹ how i connect sheet of another outside sheet Count values from cells in different columns ›
»

thanks for the reply..that

Submitted by harish on 21 November, 2012 - 10:36.

thanks for the reply..that helped me a lot..but when i am trying to get auto fill column in java using apache poi i m getting formula for that cell instead of value it has..how to get that auto fill value...

»

RE: Auto fill a column based on three columns

Submitted by manny on 16 November, 2012 - 11:10.

Hi,

Here's some example:

Sheet2 - which contains all data:

 

  A B C D
1 Product Name Version Size Rate
2 product3 version3 size3 rate3
3 product1 version1 size1 rate1
4 product4 version4 size4 rate4
5 product5 version5 size5 rate5
6 product2 version2 size2 rate2
7 … … … …

 

Sheet1 - in which you auto fill Rate column:

 

  A B C D
1 Product Name Version Size Rate
2 product1 version1 size1 = see below formula
3 product2 version2 size2  
4 product3 version3 size3  
5 product4 version4 size4  
6 product5 version5 size5  
7 … … … …

 

In cell D2 use the following formula:

 = INDEX(Sheet2!$D$2:$D$102, MATCH(1, (Sheet2!$A$2:$A$102 = $A2) * (Sheet2!$B$2:$B$102 = $B2) * (Sheet2!$C$2:$C$102 = $C2), 0))

Note: This is array formula and after enter it you must press CTRL + SHIFT + ENTER instead only ENTER.

Expand the formula to the end of your data range. This example is limited to 100 rows ($D$2:$D$102, $A$2:$A$102, ...). Change it to match your needs.

This is an example of using INDEX + MATCH instead VLOOKUP. For more information see the following topic:

 www.excelexperts.com/Excel-Tips-VLOOKUP-INDEX-MATCH

 

Best regards.

»

Thanks for the reply..that

Submitted by harish on 16 November, 2012 - 12:06.

Thanks for the reply..that solved my problem and 1 small issue is left i.e,for same product there may be several versions so,if i select a product, in versions column i should get all versions available for that product for this product column in sheet2 contains redundant names of products for different versions.

Once again thanks for the reply..if possible pls respond to this issue as well this will solve my entire issue

»

Thanks for the reply..that

Submitted by harish on 16 November, 2012 - 12:05.

Thanks for the reply..that solved my problem and 1 small issue is left i.e,for same product there may be several versions so,if i select a product, in versions column i should get all versions available for that product for this product column in sheet2 contains redundant names of products for different versions.

Once again thanks for the reply..if possible pls respond to this issue as well this will solve my entire issue

»