Data in one column to equal different data in another

[StorageQueen24]

Hello,

In column B I have a list of unit sizes 10x10, 10x15, 10x20 etc. and in column G is the price of that unit size. They are not in order on the spreadsheet. I want to type the data in column B and have it automatically populate the rate into column G. Is that possible? The prices change monthly and it would be nice if I didn't have to go cell by cell to change them.

 

[awoohaw]

welcome to the forum. I am guessing you are typing the sizes into a 3rd column and wanting the lookup to go into a 4th column?
Otherwise, I'm not sure what you mean? Are rate and price referencing the same cell value?

Maybe put a validation list drop down in a cell, and a formula next to it:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Size					Price
2		10 x 10					100			10 x 15	120
3		10 x 15					120
4		15 x 15					140
5		15 x 20					180
6		20 x 20					200
Sheet3

Cell Formulas
Range	Formula
K2	K2	=XLOOKUP(J2,$B$2:$B$6,$G$2:$G$6,"not found",0)

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=$B$2:$B$6

 

[StorageQueen24]

This is a sample of my spreadsheet.

I want to change the price of all 5x10 to read $85. We may change the prices later and I would like to change all 5x10 to $95.

 

[awoohaw]

1. Click anywhere in that grid of data,
2. Go to Data Ribbon,
3. Click Filter (the funnel Icon)
4, Click the drop down by the size,
5. uncheck the "select all" check box
6. Find the size you want (5x10) click that check box
7. Click OK
8. Select all of the prices column (not the header row)
9. Type the new price
10. Hold The CNTL Key down and Press Enter

(in step 1, if you have blank columns and rows you need to select the entire data grid, and not just one cell).

If you have a separate master table with your prices.
you can copy this formula in the price column:
(assuming youre in row 2, and size is column B
in the price column (column G???) row 2:
so, in cell G2:

=xlookup(B2,"LookupTableSize","LookUpTablePrices",0)

 

[StorageQueen24]

1. Click anywhere in that grid of data,
2. Go to Data Ribbon,
3. Click Filter (the funnel Icon)
4, Click the drop down by the size,
5. uncheck the "select all" check box
6. Find the size you want (5x10) click that check box
7. Click OK
8. Select all of the prices column (not the header row)
9. Type the new price
10. Hold The CNTL Key down and Press Enter

(in step 1, if you have blank columns and rows you need to select the entire data grid, and not just one cell).

If you have a separate master table with your prices.
you can copy this formula in the price column:
(assuming youre in row 2, and size is column B
in the price column (column G???) row 2:
so, in cell G2:

=xlookup(B2,"LookupTableSize","LookUpTablePrices",0)

Perfect! Thank you!

 

[awoohaw]

Perfect! Thank you!

My pleasure, best wishes.