Formula Needed

[Gamermatt]

Hello,

In column A, I have a list of text (not unique). In column B, I have prices that match with the text in column A. Although, many of these cells in column B are empty. I need a formula to put into column C that will look up the text from the corresponding cell in column A, and search the rest of column A for the cells with the same text. If it finds one, it should copy that price and put it into the cell.

Before:

Excel Workbook
	A	B
1	434.99|579.99|Rear Bumpers	460
2	434.99|579.99|Rear Bumpers	*
3	434.99|579.99|Rear Bumpers	*
4	434.99|599.99|Rear Bumpers	*
5	434.99|599.99|Rear Bumpers	500
6	434.99|599.99|Rear Bumpers	*
Sheet1

After:

Excel Workbook
	A	B	C
1	434.99|579.99|Rear Bumpers	460	460
2	434.99|579.99|Rear Bumpers		460
3	434.99|579.99|Rear Bumpers		460
4	434.99|599.99|Rear Bumpers		500
5	434.99|599.99|Rear Bumpers	500	500
6	434.99|599.99|Rear Bumpers		500
Sheet1

Matt

 

[T. Valko]

Hello,

In column A, I have a list of text (not unique). In column B, I have prices that match with the text in column A. Although, many of these cells in column B are empty. I need a formula to put into column C that will look up the text from the corresponding cell in column A, and search the rest of column A for the cells with the same text. If it finds one, it should copy that price and put it into the cell.

Before:

Excel Workbook
	A	B
1	434.99|579.99|Rear Bumpers	460
2	434.99|579.99|Rear Bumpers	*
3	434.99|579.99|Rear Bumpers	*
4	434.99|599.99|Rear Bumpers	*
5	434.99|599.99|Rear Bumpers	500
6	434.99|599.99|Rear Bumpers	*
Sheet1

After:

Excel Workbook
	A	B	C
1	434.99|579.99|Rear Bumpers	460	460
2	434.99|579.99|Rear Bumpers		460
3	434.99|579.99|Rear Bumpers		460
4	434.99|599.99|Rear Bumpers		500
5	434.99|599.99|Rear Bumpers	500	500
6	434.99|599.99|Rear Bumpers		500
Sheet1

Matt

Try this...

<b>Sheet1</b>

Book1
	A	B	C
2	434.99|579.99|Rear Bumpers	460	460
3	434.99|579.99|Rear Bumpers	_	460
4	434.99|579.99|Rear Bumpers	_	460
5	434.99|599.99|Rear Bumpers	_	500
6	434.99|599.99|Rear Bumpers	500	500
7	434.99|599.99|Rear Bumpers	_	500
Sheet1

This array formula** entered in C2 and copied down:

=IF(B2="",INDEX(B$2:B$7,MATCH(1,IF(A$2:A$7=A2,IF(B$2:B$7<>"",1)),0)),B2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Gamermatt]

Try this...

This array formula** entered in C2 and copied down:

=IF(B2="",INDEX(B$2:B$7,MATCH(1,IF(A$2:A$7=A2,IF(B$2:B$7<>"",1)),0)),B2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thank you

 

[T. Valko]

Thank you

You're welcome!

 

[West Man]

Another way: =SUMPRODUCT(--(A$1:A$6=A6),--($B$1:$B$6<>""),($B$1:$B$6))