Issue with the INDEX formula

[studio52]

Please help,

I can't make my formula to work. I need to match in the Sheet "2012" in the row S the information from Sheet "Unloading" from the cells A, B and C.
I need to see only the C row information(the amount in tons) in the S row, but it has to recognise the A row and B row.

Very poor explanation i know, but i still hope someone can help me.

Please, its urgent.

[TMS]

I'm not entirely sure what you are trying to do ... it's all Estonian to me

However, on Sheet '2012' in cell S2

=INDEX(Unloading!B$2:B$10000,MATCH($D2,Unloading!$A$2:$A$10000,0))

... will match the contents of cell D2 with column A on Sheet 'Unloading' and return the content of column B on Sheet 'Unloading'.

Note that the INDEX column reference, B$2:B$10000, is "relative" so you can drag this across to return the content of column C.

Also note that the search item in the MATCH, $D2, has a relative row reference meaning you can drag the formula down and it will auto adjust.


Regards, TMS

[daddylonglegs]

Does this do what you want?

=LOOKUP(2,1/(D2=Unloading!A$2:A$100)/(SUBSTITUTE(F2," ","")=Unloading!B$2:B$100),Unloading!C$2:C$100)

It matches the column D value in the current row against column A in umloading sheet, and also column F value against column B in unloading sheet (although there's not an exact match so I used SUBSTITUTE to remove spaces) and then the formula returns the tonnes from the relevant row

[studio52]

Does this do what you want?

=LOOKUP(2,1/(D2=Unloading!A$2:A$100)/(SUBSTITUTE(F2," ","")=Unloading!B$2:B$100),Unloading!C$2:C$100)

It matches the column D value in the current row against column A in umloading sheet, and also column F value against column B in unloading sheet (although there's not an exact match so I used SUBSTITUTE to remove spaces) and then the formula returns the tonnes from the relevant row

Unfortunately there is some kind of a error and i cannot explain where is it exactly.

[studio52]

This actually works, but not perfectly. I had to change the formula to be like that:

=INDEX(Unloading!C$2:C$10000;MATCH($F2;Unloading!$B$2:$B$10000;0))

But when its doing the calculation it is giving to all the calculated cells the same value. Unfortunately the formula you gave me was calculating the amonut of the tons wrong and i decided to link it better to the B row in Unloading sheet. But doesnt work anyway

Any ideas what im doing wrong?

[daddylonglegs]

If you get the same results in all rows when you drag the formula down then you may have calculation set to "manual". Press F9 to re-calculate or set to automatic

Tools > Options > Calculation

[studio52]

Its set to automatic but anyway calculates to all the same amount

I have 1799 entries and it is showing some numbers only on those what have a space in the row B sheet 2012. It fails on the function to substitute the space in the B row. Any other chance?

[studio52]

I think it would be better if I show you the exact table im working with. I tried to put your formulas into it.

Please have a look IPEC shipments2012 by Volodjka as per S+R2.xlsx

[daddylonglegs]

Whta result are you expecting to get in S2? From your data it doesn't look like there is a match for either D2 or F2

[studio52]

Sheet Unloading Cell C - Sheet 2012 Cell E. But has to be checked if the reference number in Sheet 2012 is equal to Cell B in Sheet Unloading.

Some reference numbers repeat.

[daddylonglegs]

Ok, I was hoping you would be more specific and tell me what result you expect to see in S2, e.g. "xyz" which is the value from cell ?? in your other sheet. I assume that the reference number is F2 - that's ЭД 943955 and I can't see a match for that anywhere in unloading sheet in column B

[studio52]

Thank you guys for the help i appreciate it very much. Im sorry for the confusion and i think it will be a to difficult formula so i will try tomorrow to create a macro and see if it will work. If not i might come back to you

[studio52]

Ok guys im back and still having this problem.

Here it goes step by step:
1. Sheet 2012 Row D has to match sheet Unloading Row A if match then(if no match ignore)
2. Sheet 2012 Row F has to match sheet Unloading Row B if match then(if no match ignore)
3. Sheet 2012 Row E - sheet Unloading Row C

[studio52]

Than you for the help i figured the formula out.

=IF(ISERROR(IF(OFFSET(INDIRECT("Unloading!B"&MATCH(D13;Unloading!$A$2:$A$67;0)+1);0;0)=F13;OFFSET(INDIRECT("Unloading!C"&MATCH(D13;Unloading!$A$2:$A$67;0)+1);0;0)-'2012 '!E13;""));" ";IF(OFFSET(INDIRECT("Unloading!B"&MATCH(D13;Unloading!$A$2:$A$67;0)+1);0;0)=F13;OFFSET(INDIRECT("Unloading!C"&MATCH(D13;Unloading!$A$2:$A$67;0)+1);0;0)-'2012 '!E13;""))