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

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
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.
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
CDEFGHIJKL
1Model 4
2Cost/Item
3Model 1Model 2Model 3Model 4Model 5Model 6Total Cost
4514352225
522524138
633433229
Total Cost
Cell Formulas
RangeFormula
L4=E4*INDEX(F4:K4,MATCH(C$1,F$3:K$3,0))
 
Upvote 0
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!
 
Upvote 0
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?

CDEFGHIJKL
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!
 
Upvote 0
Glad you could fix your problem with the INDEX-MATCH combination ...:wink:
 
Upvote 0
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
CDEFGHIJKL
1Model 44
2Cost/Item
3Model 1Model 2Model 3Model 4Model 5Model 6Total Cost
4514352225
522524138
633433229
Total Cost
 
Upvote 0
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

CDEFGHIJKL
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
CellFormula
D1=MATCH(C1,F3:K3,0)
L4=E4*INDEX(F4:K4,D$1)

<tbody>
</tbody>

<tbody>
</tbody>

I like that a lot! Thanks again!
:beerchug:
 
Upvote 0
I like that a lot! Thanks again!
:beerchug:
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top