INDEX AND MATCH HELP

[sureshsonti]

HELLO,
Looking for some help with index and match functions

I have to extract the rate number from haulers table to the green column based on VENDOR, MATERIAL PLANT and GL from the haulers table. The QTY (red column) should also be based on Vendor, Material Plant and GL.

Thanks for all your help.

 

[bosco_yip]

S9, copied down :

=IFERROR(INDEX(haulers,MATCH(1,INDEX((R9=haulers[GL])*(Q9=haulers[MATERIAL PLANT])*(P9=haulers[VENDOR]),0),0),MATCH(S$8,haulers[#Headers],0)),"")

Regards
Bosco

 

[Peter Bartholomew]

The attached is Bosco's solution; I claim no credit. I wondered why, when all the reference data was organised using Tables, the output reverted to cell referencing (in part).

I then thought the red column was in error only to realise that
@sureshsonti had requested a formula for that column too.

The formula is identical to Bosco's with the exception of the reference to the column header. It strikes me that a helper column in the output table giving the relevant record number for the hauler's table would both provide a useful check and, at the same time, simplify the other formulas.

 

[sureshsonti]

S9, copied down :

=IFERROR(INDEX(haulers,MATCH(1,INDEX((R9=haulers[GL])*(Q9=haulers[MATERIAL PLANT])*(P9=haulers[VENDOR]),0),0),MATCH(S$8,haulers[#Headers],0)),"")

Regards
Bosco

Thanks Mr. Bosco. This was a great solution for my problem. Thanks again for your help

 

[sureshsonti]

The attached is Bosco's solution; I claim no credit. I wondered why, when all the reference data was organised using Tables, the output reverted to cell referencing (in part).

I then thought the red column was in error only to realise that
@sureshsonti had requested a formula for that column too.

The formula is identical to Bosco's with the exception of the reference to the column header. It strikes me that a helper column in the output table giving the relevant record number for the hauler's table would both provide a useful check and, at the same time, simplify the other formulas.

Thanks for your help Peter.

 

[Peter Bartholomew]

@bosco_yip

I noticed a 'surplus' INDEX(…, 0) lurking in your formula so, naturally, I tried removing it. Turned out it was part of your repertoire of tricks to avoid the need for CSE. Thanks for the education anyway.​

@sureshsonti

I then carried on playing. I often use a defined name to ensure formulas are evaluated as arrays without CSE. In this case I also tried a trick picked up from this forum of deliberately generating an error for failed matches. The name I introduced was 'MatchedRecord' which I defined to refer to:

= 1 / ( haulers[GL]=output[@GL]) / (haulers[VENDOR]=output[@VENDOR])
[Lots of #DIV/0!s. I have left MATERIAL out because it is looked up from GL]

Rather than using MATCH to locate the 1, followed by INDEX, I have gone straight for the result using LOOKUP. I also addressed the field by name rather than looking it up in the header block.

This is probably a step or two beyond your comfort zone but I hope it is of interest.