Check existence and display result

[vijay.vizzu]

Hi...All


I have an two sheets namely DPS & RFQ, DPS is master data sheet and RFQ is some details to be extracted from master data sheet by using index match combination function. The formula i am using is =INDEX('DPS'!$R$11:$R$38,MATCH($C6&COLUMNS($H$5:I$5),'DPS'!$BK$11:$BK$38,0),0). It works fine and getting the values from master data, but while dragging the formula horizontally, then it shows some repeated values (becoz it already repeats in master sheet).


Now i wish to do that, first the formula checks the value existance in that row, if it exists then it should display blank otherwise returns the formula result. To do this i applied if formula to check current cell with its previous cell in a row by using = sign, it works fine, but it just comparing current cell with the single cell, i wish to check current cell wiht the range.


I hope you got my point, and try to suggest some solutions to do this


thanks

vijay

 

[Luke M]

Let's assume first formula is in B2. Your formula would then be:

=IF(ISNUMBER(MATCH(oldFormula,$A2:A2,0)),"",oldFormula)


Plugging in the actual formula, this becomes:

=IF(ISNUMBER(MATCH(INDEX('DPS'!$R$11:$R$38,MATCH($C6&COLUMNS($H$5:I$5),'DPS'!$BK$11:$BK$38,0),0),$A2:A2,0)),

"",INDEX('DPS'!$R$11:$R$38,MATCH($C6&COLUMNS($H$5:I$5),'DPS'!$BK$11:$BK$38,0),0))