Hlookup - Is this the best way to do what I need?

[RJSIGKITS]

Hi Guys.

So, I have a sheet that has a 'Shopping list' with a load of products that need to be ordered for a specific 'Model'.
Column A = item description
Columns B-D (random other info)
Column E = cost price per item.
Columns F to K = Qty - all columns have the model names in Row3 (F3:K3), and the columns below (F4:K75) show the qty of product that is required for that specific model.
Column L needs to return the total cost of the qty of products required.

Cell C1 is where the user simply selects which Model is to be Quoted..

"=HLOOKUP($C$1,$F$3:$K$75,2,FALSE)*E4" seems too cumbersome to keep changing the Row index manually on each line, plus if I need to add extra rows, this will mess up the whole thing.

Can anyone help me with what the best way to do this would be, please?

The forum won't allow me to attach an example!

Thanks in advance.

 

[James006]

Hi,

By all means the best way (ie the most flexible ...) to go about your issue ... is to use the INDEX-MATCH combination ...:wink:

Take a look at the following explanation:

https://exceljet.net/formula/basic-index-match-exact

Hope this will help

 

[Peter_SSs]

The forum won't allow me to attach an example!

But it does allow copyable screen shots like this. My signature block below has help with that.

Does this, copied down, do what you want?

Book1
	C	D	E	F	G	H	I	J	K	L
1	Model 4
2			Cost/Item
3				Model 1	Model 2	Model 3	Model 4	Model 5	Model 6	Total Cost
4			5	1	4	3	5	2	2	25
5			2	2	5	2	4	1	3	8
6			3	3	4	3	3	2	2	9
Total Cost

Cell Formulas
Range	Formula
L4	=E4*INDEX(F4:K4,MATCH(C$1,F$3:K$3,0))

 

[RJSIGKITS]

Thank you, James

using INDEX and match I totally missed!
I did keep trying with LOOKUP and match, but completely didn't think about INDEX!

Thank you!

 

[RJSIGKITS]

But it does allow copyable screen shots like this. My signature block below has help with that.

Does this, copied down, do what you want?

	C	D	E	F	G	H	I	J	K	L
Model 4

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

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

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Cost/Item[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Model 1[/TD]
[TD="align: right"]Model 2[/TD]
[TD="align: right"]Model 3[/TD]
[TD="align: right"]Model 4[/TD]
[TD="align: right"]Model 5[/TD]
[TD="align: right"]Model 6[/TD]
[TD="align: right"]Total Cost[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]

</tbody>

Total Cost​

[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] "]L4[/TH]
[TD="align: left"]=E4*INDEX(F4:K4,MATCH(C$1,F$3:K$3,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

All advice is perfect here, thanks!
Why I didn't try with INDEX, I'll never know!

Your line is 100% what I need!

 

[James006]

Glad you could fix your problem with the INDEX-MATCH combination ...:wink:

 

[Peter_SSs]

All advice is perfect here, thanks!
Why I didn't try with INDEX, I'll never know!

Your line is 100% what I need!

You are welcome.

Since all rows are using the same model column, you could save some processing power and simplify the formulas by just calculating the MATCH once (I've used cell D1) instead of on every line & then only using INDEX in each row.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
1	Model 4	4
2			Cost/Item
3				Model 1	Model 2	Model 3	Model 4	Model 5	Model 6	Total Cost
4			5	1	4	3	5	2	2	25
5			2	2	5	2	4	1	3	8
6			3	3	4	3	3	2	2	9
Total Cost

 

[RJSIGKITS]

You are welcome.

Since all rows are using the same model column, you could save some processing power and simplify the formulas by just calculating the MATCH once (I've used cell D1) instead of on every line & then only using INDEX in each row.

Total Cost

	C	D	E	F	G	H	I	J	K	L
Model 4

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:65px;"><col style="width:26px;"><col style="width:75px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:65px;"><col style="width:78px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

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

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

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

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

[TD="align: right"]Model 1[/TD]
[TD="align: right"]Model 2[/TD]
[TD="align: right"]Model 3[/TD]
[TD="align: right"]Model 4[/TD]
[TD="align: right"]Model 5[/TD]
[TD="align: right"]Model 6[/TD]
[TD="align: right"]Total Cost[/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]

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

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D1 =MATCH(C1,F3:K3,0) L4 =E4*INDEX(F4:K4,D$1) <tbody> </tbody>

<tbody>
</tbody>

I like that a lot! Thanks again!

 

[Peter_SSs]

I like that a lot! Thanks again!

Cheers.

BTW, best not to fully quote longish posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.