Index match and mutiply

[andrew_milonavic]

Hey Everyone,

I have a formula that text joins three cells using index/match for each. The second cell being joined is a number. Is it possible to have the number being returned by the index/match multiplied by a number in another cell?

If my current formula returned: Truck 5 Cat. The new formula would return: Truck 10 Cat. Assuming the cell to multiple by had 2 in it.

I have the above working with vlookup, but with the amount of data it's slow to load and calculate so I'm trying to convert to index/match.

Hope that makes sense.

Thanks

Andrew

 

[MrTeeny]

Have you simply tried multiplying the match by two ? or even by the cell (=A1 & " " & VLOOKUP(F1,B1:D4,3)*G1 & " " & C1, =A1 & " " & VLOOKUP(F1,B1:D4,3)*2 & " " & C1 etc)

 

[andrew_milonavic]

Hi MrTeeny,

When calculating the match by two or the cell by 2 it just moves the column. So If the formulas column is 3 than it becomes column 5 (If multiplied by 2).

I can make the vlookup work, but with the amount of data it slows my excel sheet. I've been told that index/match is faster, so I'm trying to convert.

Thanks

Andrew

 

[FDibbins]

...When calculating the match by two or the cell by 2 it just moves the column....

You are probably putting the "*2" in the wrong place.
assuming you have something like this...
=(1st-formula)&" "&(2nd-formula)&" "&(3rd-formula)
you need to adjust that to this...
=(1st-formula)&" "&(2nd-formula)*&$Z$1" "&(3rd-formula)
This assumes that your multiplier is in Z1

 

[andrew_milonavic]

Hey FDibbins,

That works perfectly.

Thank you!!!!

Andrew

 

[FDibbins]

Im happy it helped