Link to home
Start Free TrialLog in
Avatar of agwalsh

asked on

index/match not pulling in all matching figures

I've attached a file and what I want to happen is the following:
On the C File tab, I have created an index match formula - which I want to do the following:
compare the PO# on the two sheets (A File and C File) and return the matching PN number. (and the quantity - but let me get one right first !)
I've tried it with the vlookup (no joy). I've tried it with the index/match but it's not picking up all the order numbers - I can see PO# on A-file that it's not picking up in the C-file. I've added conditional formatting to show where they match...what am I missing? Originally the two files had their PO# formatted differently (but they are both general now) ...thanks for your help.
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

In the sheet which the VLOOKUP should search to find a match make sure that the lookup column data is sorted
Avatar of Martin Liss
You have forgotten to attach the file.
Avatar of agwalsh


@MartinLiss (well DOH! for me :-) ) thx for letting me know...
it should be

=INDEX($B$2:$B$94,MATCH('A File'!A2,'A File'!$A$2:$A$73,FALSE))

You forgot to put '$' sign on this part


Also, on your A-file, the POs are still showing as text

Select the Column A

Goto data tab, click 'text to columns'

Select 'Fixed Width' then click finish to convert to General - this will make the vlookup work
Avatar of agwalsh


@shanan212 - thanks for that. I also noticed that I had a different length for the index part and the Match part. It's odd about the A file thing because on mine it shows as General but I have done what you suggested but it just seems to pick up the first PN number only - I've attached the file
You should use this formula in E2

=INDEX($B$2:$B$94,MATCH($A2,'A File'!$A$2:$A$94,FALSE))

and copy it down and across

=INDEX(B$2:B$94,MATCH($A2,'A File'!$A$2:$A$94,FALSE))
Yes. Vlookup only picks up the 1st occurance and thats a downside of it.

There is way to go around it but it would be much more complicated since you could potentially have 100s of POs.

Another way is to use 'unique column' or a helper column

In both files, insert a column in A, create a unique value based on PO & quantity and then lookup this value.

eg: in A2 of A-file, putting


In A2 of C-file, putting


Then looking up the unique colum on C file on A file.

However, there is a downside...

If there is same POs with same quantity, it wouldnt pick up.

To get out of this dilemma, you could re-analyze why you would need the 'vlookup' formula or introduce another value to both tabs to make the 'unique' columns more unique.
Avatar of agwalsh


@shanan12 or could I use some combination of PO # and PN number to generate a unique lookup field? But I'm using index/match rather than vlookup...I thought that would get around that...
I'm sure I can do what you want using VBA code in a macro but, and I'm embarrassed to say this, I don't understand exactly what you want including  what you want to do with quantity, so could you explain with some detail please?
Of course! I suggest using all 3 PO, quantity and PN to make the lookup return exactly what it is looking for.
My article here explains techniques for pulling the Nth lookup value from a list.

If you already know ahead of time that there are, at most, say 10 values to return, you can set up formulae to return the 1st through 10th items, wrapping the formulae in IFERROR to handle the error that will result when you try to do something like pull the 7th item when there are only 6 to pull.
Avatar of agwalsh


Hm, well, what I'd ideally like would be something where the formula would pull in the matching PO numbers and then the different PN numbers for it. See attached file (CFile - desired solution)
Avatar of Shanan212
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of agwalsh


@shanan212 - that looks much closer to what I thing - I presume a blank cell means no match - but an entry does mean a match???
Correct. I made it so that if there is no match/error, it would return blank.
Avatar of agwalsh


This ticks a lot of boxes and I'm delighted with it :-)