Return list based on vlookup values

I'm not sure if this question has been asked yet or not. I'm not really even sure how to ask it since I am an amateur when it comes to excel. I know enough to be dangerous.

I've created a quoting sheet where I have plugged in the necessary materials and costs for each type of fence available. What I would like to do is produce a materials list based on the results. So far, I have created a table with Fence type on X and Material type on Y. Instead of just showing the number that it calculates, I would like a list of calculated number for each material type so that a materials list can be printed or emailed. Is this possible? If this question has already been answered, please forgive me! Can you provide the link to the thread?

Attach a small representative sample workbook.

Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for the quick response. I have uploaded the attachment, but my attachment list is empty.

I checked the FAQ. Did everything correctly based on that...

It works now... Strange...

Now i can't get rid of the image. Oh well. Attachment is at the bottom.

Where are the desired results?

In the table. I can't tell you the exact location because uploading it corrupted the file on my computer for some reason. I think it starts in column J. What I would like is for the list to produced to show, for example,
44 of 68 Post
270 of 28 Rail
Etc...

If 6"X6"X6' Charleston is chosen, I would like a list of the materials required for that selection.

Originally Posted by Glenn Kennedy

K6 to U10.... I think....

Yes. These are the results.

First off, I would convert the table into a range. Click on any of the cells in the table > Design > Convert to Range.

If you are looking for the "MATERIALS LIST" to change depending on the selection of your drop-down in K14, try this in A29:

=IFERROR(INDEX(K$5:U$5,SMALL(IF((K$14=J$6:J$10)*(K$6:U$10>0),COLUMN(K$5:U$5)-(COLUMN(K$5)-1)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula down as far as needed.


As for the values in the matrix (K6:U10), the values don't seem to line up. How does 6"X6"X6' Charleston and 68 POST = 44? You are showing that the 44 comes from A6 but A6 corresponds to 6 X 6 X 8...

Thanks for the info!! I'll try it out.

I'm not sure what your question is. But, 68 Post is a 6"X6"X8' post which is what is required to build a 6' tall fence because 2' goes in the ground. I have simplified the material names in the chart to save space since, ultimately, it will be listed on the app I created (with help from a free service).

If 68 post is a 6"X6"X8' post, then why (according to your sample data) is the 44 next to 6"X6"X6' Charleston and 6"X6"X6' Standard but not the other three?

Keep in mind that I do not know what these names mean.

Just to be clear, you are looking for a formula to produce the values in K6:U10, correct?

The first two numbers in the description are the dimensions of the post used in the fence. So, 6X6X6 Charleston and 6X6X6 Standard both use a 6"X6"X8' wood post. The 4X4X6 Charleston and Standard both use a 4"X4"X8' wood post and the 6X6X8 Charleston uses a 6"X6"X10' wood post. That being said, they all use the same number of posts which is why 44 shows up in every category. The posts are set every 8' so, in this example, 320 feet divided by 8 feet plus 15% overage with a ceiling of 1 is 44.

The reason I am struggling with this whole thing is I'm a builder, not a programmer. But I do love to play in excel.

Originally Posted by 63falcondude

Just to be clear, you are looking for a formula to produce the values in K6:U10, correct?

Yes. The formula you gave me does produce the names in the column headers, but not the quantities required for the job. Ideally, I'd like the formula you gave me in one column and then the quantities related to it in the adjacent column.

Ideally, I'd like the formula you gave me in one column and then the quantities related to it in the adjacent column.

Oh! I thought that you wanted us to change the formulas in the K6:U10 matrix...

A29 =IFERROR(INDEX(K$5:U$5,SMALL(IF((K$14=J$6:J$10)*(K$6:U$10>0),COLUMN(K$5:U$5)-(COLUMN(K$5)-1)),ROWS($1:1))),"") Ctrl Shift Enter
B29 =IF(A29="","",SUMPRODUCT((K$5:U$5=A29)*(K$6:U$10)))

Drag the formulas down as far as needed.

Originally Posted by 63falcondude

Oh! I thought that you wanted us to change the formulas in the K6:U10 matrix...

A29 =IFERROR(INDEX(K$5:U$5,SMALL(IF((K$14=J$6:J$10)*(K$6:U$10>0),COLUMN(K$5:U$5)-(COLUMN(K$5)-1)),ROWS($1:1))),"") Ctrl Shift Enter
B29 =IF(A29="","",SUMPRODUCT((K$5:U$5=A29)*(K$6:U$10)))

Drag the formulas down as far as needed.

The B29 formula seems to be providing an aggregate total of each column. So, next to 68 Post, it shows 88 instead of 44 because there are 2 values of 44 in column K. I'm guessing that is because of the SUMPRODUCT command? Is there a display value command?

I really appreciate the help! Looks like you're a whiz with the formulas.

I thought that you wanted the sum. Not sure what I was thinking there...

Try this instead:

B29 =IF(A29="","",INDEX(K$6:U$10,MATCH(K$14,J$6:J$10,0),MATCH(A29,K$5:U$5,0)))

Perfect!! Thank you so much!!!! I think when I retire from building, I'll hire you to teach me about excel.

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.