Multiple criteria lookups (INDEX + MATCH?)

Hi

I've read various articles on the web about how to use INDEX and MATCH to return a value using several criteria but I just cant seem to get mine to work! Driving me mad

It would be a massive help to me if someone can help me sort this

Ok, I have a list of Purchase order numbers and the quantities of item ordered on that particular order number

On a seperate sheet I have a list of thousands of invoices that has the purchase order number, invoiced value and invoiced quantity.

Now, as some of the purchase order numbers are used more than once (on both sheets) the only way I can match the invoice VALUE to the correct PO is by matching both the Purchase order number AND the quantity.

So, I used the following forumula:

{=INDEX(Sheet2!O1:O500000,MATCH(1,(Sheet2!B1:B500000=A2)*(Sheet2!C2:C500000=B2),0))}

Explanation:
Sheet 2 is the sheet with the invoice informaton on, O is the column in which the invoice values are (The value I wish to return)
Column B in sheet 2 is where the purchase order number is
Column C in sheet 2 is where the quantity is
Cell A2 is the PO number I want to find a match for
Cell B2 is the quantity I want to find a match for

Matching both the PO number and PO quantity against the invoiced PO number and invoiced quantity will then tell me how much was invoiced against that particular PO

Just keep getting a #REF or #NA error!

Massive thank you to anyone who helps!

Originally Posted by nflsales

{=INDEX(Sheet2!O3:O500000,MATCH(1,(Sheet2!B3:B500000=A2)*(Sheet2!C3:C500000=B2),0))}
Try this

Still doesnt work

Originally Posted by :) Sixthsense :)

Use SumifS()

=SUMIFS(Sheet2!O1:O500000,Sheet2!B1:B500000,A2,Sheet2!C1:C500000,B2)

Cant use SUMIFS because some of the PO's are duplicated, and on the quantity may be exactly the same - so it adds together to values of both POs because the quantity and po is same.

Thanks though!

Originally Posted by :) Sixthsense :)

Use SumifS()

=SUMIFS(Sheet2!O1:O500000,Sheet2!B1:B500000,A2,Sheet2!C1:C500000,B2)

Cant use SUMIFS because some of the PO's are duplicated, and on the quantity may be exactly the same - so it adds together to values of both POs because the quantity and po is same.

Thanks though!

Hi,
try this
{=INDEX(Sheet2!O3:O500000,MATCH(a2&B2,Sheet2!B3:B500000&Sheet2!C3:C500000,0))}

Punnam

Originally Posted by :) Sixthsense :)

Then nflsales suggestion should work for you.

Please share a screenshot of the formula or attach a sample file

Apologies - this does work, but I'm getting #N/A when putting $ round the columns as I dont the fields to move when I drag the formula down

How do I get around this?

Thanks

So for example, this is actually the foruma copied from the sheet:

=INDEX('INV data'!$O$5:$O$22434,MATCH(1,('INV data'!$C$4:$C$22434=E4)*('INV data'!$L$4:$L$22434=I4),0))

Its worked using this formula:

=INDEX('INV data'!$O$3:$O$22432,MATCH(1,('INV data'!$C$2:$C$22432=E4)*('INV data'!$L$2:$L$22432=I4),0))

But it removes the { before and after when I drag the formula down?

And its worked a treat, thank you very much all. rep will be added

Originally Posted by :) Sixthsense :)

If you drag it then it won't get removed!

I thought it had done it... Here is my formula:

{=INDEX('INV data'!$O$3:$O$22432,MATCH(1,('INV data'!$C$2:$C$22432=E2859)*('INV data'!$L$2:$L$22432=I2859),0))}

But its actually returning the value from 1 line BELOW of the matched critera? So lets say I want the value in O and the criteria is matched on line 50, its actually returning the value in O on line 51

Originally Posted by :) Sixthsense :)

Because of wrong reference provided in index array part 'INV data'!$O$3:$O$22432.

Adapt it to suit the other references like this 'INV data'!$O$2:$O$22432

Apologies, as soon as I asked the question I'd realised what I'd done wrong!

If I wanted to put a 3rd criteria in, is that possible? Im trying to use the following formula but coming up for with NA:
{=INDEX('INV data'!$O$1:$O$22431,MATCH(1,('INV data'!$C$1:$C$22431=E1)*('INV data'!$L$1:$L$22431=I1)*('INV data'!$E$1:$E$22431=C1),0))}

Originally Posted by :) Sixthsense :)

Have you entered it as array formula?

If so then there is not match found in that referred ranges

Yes I've entered as an array

There is though, if I look manually for the PO number, I can see the qty and item description match, but its still coming back is N/A

If my formula is right then it must be something else messing it up

Originally Posted by :) Sixthsense :)

Check whether the calculation process is interrupted Or it is completed fully.

If the calculation is interrupted then you will see calculate message in the excel status bar.

Or

Check whether the cell possess any extra spaces which cannot be identified in the normal eye

Had a space in! Quick find and replace sorted that

Thanks so much for your help!!!!