Vlookup - Same Reference, Different Value

[Elaine10]

Hi Everyone

I'm hoping someone can help me with this.... I have 2 sheets

1. ESTIMATOR CALC
2. QUOTE FRONT

I'm trying to get specific info to reference from my ESTIMATOR CALC to my QUOTE FRONT.

The formula I currently have in there works for item No. 1, but doesnt work all the way down up to item No. 3....

* Current formula being used
=IF($A47="", "", IFERROR(INDEX(EstimatorCalc_ColC, SMALL(IF('ESTIMATOR CALC'!$A$48:$A$1000=$A47, ROW('ESTIMATOR CALC'!$A$48:$A$1000)-ROW('ESTIMATOR CALC'!$A$48)+1, ""), ROWS($A$47:$A47))), ""))

* Another formula I have also used and it also worked but ALSO didnt pull through the values past cell 50 in the QUOTE FRONT
=IFERROR(INDEX('ESTIMATOR CALC'!$C$48:$C$1000, SMALL(IF($A$47='ESTIMATOR CALC'!$A$48:$A$1000, ROW('ESTIMATOR CALC'!$A$48:$A$1000)-ROW('ESTIMATOR CALC'!$A$48)+1, ""), ROWS($A$1:$A1))), "")

* This formula actually worked the best so far, however whenever i added or removed rows/columns it would stop working
=IFERROR(IF(INDEX('ESTIMATOR CALC'!$C$48:$C$1000, SMALL(IF($A$47:$A$999='ESTIMATOR CALC'!$A$48:$A$1000, ROW('ESTIMATOR CALC'!$A$48:$A$1000)-ROW('ESTIMATOR CALC'!$A$48)+1, ""), ROWS($A$1:$A1)))=0, "", INDEX('ESTIMATOR CALC'!$C$48:$C$1000, SMALL(IF($A$47:$A$999='ESTIMATOR CALC'!$A$48:$A$1000, ROW('ESTIMATOR CALC'!$A$48:$A$1000)-ROW('ESTIMATOR CALC'!$A$48)+1, ""), ROWS($A$1:$A1)))), "")

 

[GlennUK]

=IF($A47="", "", IFERROR(INDEX(EstimatorCalc_ColC, MATCH($A47,EstimatorCalc_ColA,0)+COUNTIF($A$1:$A46,$A47)),"")) as a guess. You can see the logic anyway.

 

[Elaine10]

Thanks Glenn