Need a formula

[vmohan1978]

Dear All,

I have 2 Sheets where in sheet-2 my co-efficient are calculated , i need a formula in Sheet -1 with respect to the Nos


For Example in C2 if i mention 2 then corresponding to sheet 2 wrt 2 i need 0.541 and 0.459 in "D" and "E" column same way if i mention 4 i should get values in D2:G2


Accordingly

[pre]

A	B	     C	      D	   E	   F	   G
1	Sl.No	Description Nos	

2			     2	  0.541	  0.459	

3			     4	  0.188   0.353    0.316   0.143

[/pre]
Please do the needful .

 

[Luke M]

You didn't tell us where those numbers are coming from. I'm guessing sheet 2, but I have no idea where on sheet 2.

SWAG at a formula:

=IF(COLUMN(A$1)<$C2,Sheet-2!A1,"")

 

[vmohan1978]

Dear Luke the values are in sheet2

a b c d e f g h

1 No

2 2 0.541 0.459

3 3 0.292 0.479 0.229

4 4 0.188 0.353 0.316 0.143

5 5 0.136 0.252 0.298 0.212 0.102

6 6 0.106 0.186 0.249 0.230 0.151 0.078

 

[Faseeh]

Hi vmohan1978,


Assuming your data is present between B1:H5 in sheet2, Excl. headers on the top and the left, this formula should work (place it on sheet 1)

[pre]

=VLOOKUP($B$1,Sheet2!$B$1:$H$5,COLUMN(Sheet2!B2))

[/pre]
...drag to the right.


Regards,

Faseeh

 

[vmohan1978]

Dear Faseeh,


It worked perfectly, thanks for your help. Can you explain why we have to mention "COLUMN"

 

[shibulal]

If I want to supress the 'zero" ?

 

[Faseeh]

Hi vmohan1978,


Thanks for your kind words. VLOOKUP() has syntex:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)


The COLUMN() function gives us the no. of column. Hence I have defined the third variable int he VLOOKUP() i.e. col_index_num by using COLUMN(). When you drag it to right, the Variable in this function will change due to variable referencing and will give you Col no.s like 1,2,3 so forth, the will be feed to col_index_num and hence it works. Hope this explanation helps. :)


@ shibulal


Hi, for the sake of this case where there are no "Zeros" originally present in the LookUp_Table, you can Custom Format the cells to: #;#;""

This will suppress all zero to blanks. But this will create problem if you have zeros in your regional table.


Regards,


Faseeh