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
materials	price	materials	price
M1	M8
M2
M3
M4
M5
M6
M7
M8
M9
M10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]80[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=VLOOKUP(D2,A:B,2,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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 ^^.