What's the best approach here? (Data Acquisition)

[mardipraxes]

Hey, I'm a beginner at excel but I was tasked with refurbishing our budget template. It's a construction company and in the budget we describe what we're doing and what materials we use and price it by units, square meters etc..

The thing is we use a lot of materials, and looking them up by hand sometimes it's too much work, I've recently got a price table of most of our materials but I have no idea how I should approach this basically I'd like a function or something that would work like a search engine, for example I'd search for bricks and I could choose the type of brick I want and the return would be the price of it.

If you guys want more info or clarification on this I can give you, but the gist of it is, I wanna ease the find of materials price, so if you know a better way to work this out I'm all ears.

David

 

[AlanY]

let say your price table is in Columns A & B, like this

Excel 2012
	A	B	C	D	E
1	materials	price		materials	price
2	M1	10		M8	80
3	M2	20
4	M3	30
5	M4	40
6	M5	50
7	M6	60
8	M7	70
9	M8	80
10	M9	90
11	M10	100
Sheet3

Cell Formulas
Range	Formula
E2	=VLOOKUP(D2,A:B,2,0)

the vlookup() formula in cell E2 will look up the material in D2 (in this case M8) from the table A:B and report the price in Column B (as 2 in =VLOOKUP(D2,A:B,2,0)). the 0 at the end forced the function to find an exact match.

 

[mardipraxes]

let say your price table is in Columns A & B, like this

Excel 2012

	A	B	C	D	E
1	materials	price		materials	price
2	M1	10		M8	80
3	M2	20
4	M3	30
5	M4	40
6	M5	50
7	M6	60
8	M7	70
9	M8	80
10	M9	90
11	M10	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =VLOOKUP(D2,A:B,2,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

the vlookup() formula in cell E2 will look up the material in D2 (in this case M8) from the table A:B and report the price in Column B (as 2 in =VLOOKUP(D2,A:B,2,0)). the 0 at the end forced the function to find an exact match.

VLOOKUP also works if the info is in another excel file, and if it does do I have to specify the sheet?

If it doesn't work I'll just copy the info into a new work sheet and go from there, the problem is that it's a lot of materials, at least 100 pages of information.

Thanks for you answer, it was really helpful!

 

[AlanY]

yes, it should work on another excel file as well.

if you have 100+ pages it's probably better to consolidate them onto 1 sheet but I'm sure you're going to say the tables are live and keep updating in regular intervals

 

[mardipraxes]

Thanks for the help already working on it ^^.