Formula to populate product ID & price from lookup list

[bs1977]

Hi

I am looking for help with the following

Attachment 740933

The task is to use a index & match formula in B12 that populates from the Product id that i can then drag down and across to also populate pack price.

Regards

BS

[Pepe Le Mokko]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are inexperienced here, is have done it for you today.)

[bs1977]

Thank you for amending the title.

I need to use the INDEX/MATCH formula to do this that is why it was mentioned how it was to be done. Sorry for any confusion

[AliGW]

Your title was generic, nonetheless, with no hint at what you were trying to do. I hope you can see the difference.

Why must it be INDEX MATCH? What if there’s a more efficient alternative? Very often people come here with an idea of how they think something should be done, but end up with something quite different. Are you saying that you will dismiss any solution that does not contain INDEX MATCH?

[bs1977]

This is the question. It isnt the way i would normally do it but this is the way they ask.

7) Create an INDEX & MATCH function to automatically fill the Invoice Generator entries for “Product Name and “Pack Price”.

The specific requirement is that in cell B12 you enter a function that is structured so that it can be entered just once and then copied to fill down and across to return the correct details for each order item.
Check the results against the list above to make sure the correct details have been found.

[AliGW]

That’s interesting.

So, is this some form of homework? And if so, what have you tried so far?

The problem with wanting to drag across and down is the lookup table: for this to work easily you’d need the product ID in the left-most column. It’s easy to do if the fromulae for product name and price are separate and just drag copied down.

[bs1977]

I am trying to help a friend. They are resitting this test and it just didnt make sense to me as it was.

I figured if i moved the PRODUCT ID column to the left this would work in a fashion.

=INDEX(K6:K25,MATCH(A12,J6:J25,0))

The problem is with the K6:K25 then becomes K7:K26 etc in the Product name column when i drag it down so when i drag accross to the Pack price column again it isnt absolute.

When i make it absolute and drag across it then obviously repeats the product name in the product price column.

I have got nowhere without moving the Product ID column in the lookup table

Thanks

[AliGW]

If you use move product ID you can use INDEX MATCH MATCH to match up the rows AND the columns. However, with the product ID column BETWEEN the two others, there is no easy way to do what you are being asked to do. What sort of level is this aiming at? I ask because making it work to drag across and down would require a bit of advanced wizardry.

[AliGW]

Actually, no - on reflection, INDEX MATCH MATCH should work.

[JohnTopley]

Product name

=INDEX($I$6:$K$25,MATCH($A12,$J$6:$J$25,0),1)

Price

=INDEX($I$6:$K$25,MATCH($A12,$J$6:$J$25,0),3)

[AliGW]

Try this in B12 and copy across and down:

=INDEX($J$6:$L$25,MATCH($A12,$K$6:$K$25,0),MATCH(B$11,$J$5:$L$5,0))

[bs1977]

It is an accountancy degree.

[bs1977]

I tried that earlier. Comes Up as #N/A
Theres actually nothing in their coursework that covers it. I thought it strange.

[wk9128]

365

Cell B12 formula , drag down

[JohnTopley]

Ali's formula works.

[bs1977]

Thats strange. I have created the sheet again and it works.

Thanks for your help

BS

[AliGW]

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

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.