Need help with formula/formulas.

Question

Hello friends,

I'd need a formula or formulas that would work something like this(based on screenshot bellow);

Formula should find and match name i type in A7 in "Material" column inside this table than take "Item1 name"(I) from same row copy it, than take value from B7 and multiply it by "Item1 quantity"(J) and put it next to item name and so on for each item there is in that row.

When formula does its thing result should look like this(based on screenshot);

First row being just what i type in(what i'm looking for)

Caesarium Cadmide	5000
Cadmium	500000
Caesium	500000
Oxygen Fuel Block	25000

I was thinking maybe 2 formulas would be better and simpler to make, one for names and one for number.

PS. In case anyone is wondering this is for EVE Online.

Answer 1

After sleeping on it, I got an idea and it now works.

=IFERROR(INDEX(I:I;MATCH($A$7;E:E;0));)

This formula works just as i needed it to work.

Now for step 2 i would need to add some kind of filter to it that would take that first result, find it on E:E list and do the same but if its not on E:E list than just copy the result. And i would have to repeat that process 5 times.

So basically i need to take =IFERROR(INDEX(I:I;MATCH($A$7;E:E;0));) and somehow make it decide whether to copy(if its not in E:E) or find new items(if it is in E:E).

Than on last one i would need to take all results with same name and show them as 1 result with sum of their quantity.

Answer 2

Thank you for your input Saurabh.

Neither of those formulas work, something is amiss.

I am not very proficient in using excel so could you please use screenshot provided in my original post to make accurate adjustments to those formulas and tell me if there is specific place i should copy them.

I am also very confused by why you used C column in first and C and K in second formula as an example since C is empty and is not suppose to be used for this, results should go in to A and B columns respectively starting at A8 for item name and B8 for item quantity, A7 should match something in E column and data should be pulled out of same matched row but from I/J, K/L, M/N (item#/quantity#) columns since those are pairs relevant to each other.

Now for what i've tried in modifying your formulas;

1. I had to replace , with ; for excel to even recognize them as formula. =INDEX($C$3:$C$100; MATCH($A7; $C$3:$C$100; 0))
2. when i pasted first formula in to A or C field i get #N/A

"Value Not Available Error

A value is not available to the formula or function."

3. I have tried replacing C with A, E, I, all of those both ways since i don't understand those formulas and i just went for wild guess

Now either i'm too stupid for this, you misunderstood my ask or you've led me on a wild goose chase as internet tends to do...

Answer 3

Hi, Certainly! Here's how you can set up the formulas to achieve the desired outcome: For the item names:

=INDEX($C$3:$C$100, MATCH($A7, $C$3:$C$100, 0))

For the quantities:

=INDEX($K$3:$K$100, MATCH($A7, $C$3:$C$100, 0))

These formulas will dynamically extract the item names and amounts based on the data entered in cell A7. Be careful to modify the table layout and range references to fit your particular worksheet configuration. Please tell me if you require any additional help!

Best Regards, Saurabh