data extraction using vlookup or index match (I NEED HELP!!!)

[UK-MAL]

i have a spreadsheet that contains data relating to purchase orders that I need to be able to extract data from using two criteria, i would use a vlookup to find the po number that matches and pull the value field across, but now in need to be able to alos match a part number and pul a specific value across. is this possible?

PO - PART - Value
1 a100 1
1 a200 2
1 a150 2
2 a100 2
2 a200 3


So in a second sheet i need to take the value for a PO and Part.
i've tied my self up in nested IF statements and got lost in INDEX-MATCH
I need help

[oeldere]

Yes it is, e.g. with pivot table.

You don't add an excel example so I can't show you.

[Tony Valko]

Try this...

Data in the range A2:C6

Use cells to hold the lookup values:

E2 = some PO like 1
F2 = some PART like a200

This array formula** entered in G2:

=INDEX(C$2:C$6,MATCH(E2,IF(B$2:B$6=F2,A$2:A$6),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[UK-MAL]

Thank you, I'm not too sure on Pivot tables, the table that I'm draging cells to has a lot more data in that I don't want lost for view but I will giv it a look.

[UK-MAL]

Thanks, I'll give this a go and plead for more help if I get stuck.

[Tony Valko]

Let us know how you make out.

[Kevin UK]

Hi UK-MAL

Two versions, assuming your data is in A1:C5.

1/ =SUMIFS(C1:C5,A1:A5,E1,B1:B5,F1)
2/ =LOOKUP(2,1/((A1:A5=E1)*(B1:B5=F1)),C1:C5)

[UK-MAL]

Attachment 208961Dear all, still being a little slow on this, I didn't make a good job of explaining what I need, I now have a copy of the workbooks,
I have put extracts of the two seperate work books onto a single sheet.
Columns A to C is the data that need to be updated by having the matched contents of column H placed in D.
The Purchase Order number have multipule entries in the second list (E:H)

ie. for the first line, cell D2 should have 249460
in it matched from H93

I hope the attachement helps me explain, or if someone could advise this old fossil how to load up the actual sheet...

Thanks again

[UK-MAL]

Cheers Biff, made a complete pigs ear of it. Had another go at asking the question and added a copy of the worksheet.

Mark

[Tony Valko]

Looks like a case of data type mismatch.

In the "index statement test" file the data in the Item column was formatted as TEXT (even though if you check the format it says General). Most of the entries in the column are in fact text strings but there a few numeric numbers in there as well. This caused a data type mismatch with the lookup criteria in column B.

I ran this macro on the Item column then everything worked as expected:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

What that macro does is it "cleans" the data of unwanted char 160 spaces and multiple interspersed char 32 space characters. One of the side benefits of the macro is that it will "clean" the data and any text numbers will be converted to numeric numbers.

I used this array formula**:

Order refers to H2:H2835
Item refers to G2:G2835
POrder refers to F2:F2835

=INDEX(Order,MATCH(A2,IF(Item=B2,POrder),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[UK-MAL]

Thank you for the additional information, I do appreciate the help.
This looks like a very comprehensive answer with the 'clean' function. this must have been what was causing me the problems.

If I have an more issues with this I'll repost.

Regards
Mark

[Tony Valko]

You're welcome!