Meet criteria in two columns on diff worksheet and populate the worksheet when user enters InvNum

[ocatrina]

Any help appreciated...Self Taught Excel Enthusiast...

=IFERROR(INDEX(DataEntry!$F$2:$F$1000, IF(AND(DataEntry!$D$2:$D$1000=$A$1,DataEntry!$C$2:$C$1000="KomRip"),ROW(DataEntry!$F$2:$F$1000)-MIN(ROW(DataEntry!$F$2:$F$1000))+1), ROWS($A$26:A26)),"")

DataEntry!f2:f1000 = PO#

DataEntry!d2:d1000=InvNum

A1 = user entered invoice number on shipment sheet to populate shipment sheet ; the trigger to extract  the correct records from the DataEntry! worksheet

DataEntry!c2:c1000 = CompanyName

Rows(a26:a26) = rows on the shipment sheet I want to populate with PO# that

match the criteria, InvNum and CompanyName.

Currently I get a Spill error.

I was using the following formula, which worked perfectly, until I wanted to add the extra criteria of needing the CompanyName AND the InvNum to match upon the user entry of the InvNum used to populate the shipment sheet.

=IFERROR(INDEX(DataEntry!$F$2:$F$1000, SMALL(IF(DataEntry!$D$2:$D$1000=$A$1,ROW(DataEntry!$F$2:$F$1000)-MIN(ROW(DataEntry!$F$2:$F$1000))+1), ROWS($A$26:A27))),"")

Very convoluted for a newby...Any help is greatly appreciated.

[#2]

@ocatrina 

Please upload a sample workbook.

[#3]

@ocatrina

[#4]

As I am looking at my own statements about my problem, would I need another cell, like B1 on the ShipDetForm, to evaluate and match the KomRip? Can A1 only do the job for matching the invoice number?

[#5]

The following formula outputs the correct amount of PO#s but the wrong ones.

{=IFERROR((INDEX(DataEntry!$F$2:$F$1000, IF(AND(DataEntry!$D$2:$D$1000=$A$1,DataEntry!$C$2:$C$1000=$B$1),ROW(DataEntry!$F$2:$F$1000)-MIN(ROW(DataEntry!$F$2:$F$1000))+1), ROWS($A$26:A26))),"")"}

[#6 · Accepted solution]

OK..After all day, I have the answer.

=IFERROR(INDEX(DataEntry!$C$2:$F$1000, SMALL(IF(COUNTIF($A$1,DataEntry!$D$2:$D$1000)*COUNTIF($B$1,DataEntry!$C$2:$C$1000),ROW(DataEntry!$C$2:$F$1000)-MIN(ROW(DataEntry!$C$2:$F$1000))+1), ROWS($A$26:A26)),COLUMN(D1)),"")

[#7]

Created $B$1 to hold "KomRip"..probably wouldn't be any different than using a literal in the formula, but more useable...just in case I want to use it for other companies. HA!

Joined the columns as a range and it was able to find the all the values desired in the selected column, D1.

LIke one big chess game...I win.....eventually.

[#8]

@ocatrina 

=FILTER(DataEntry!F:F,((DataEntry!C:C=$B$1)*(DataEntry!D:D=$A$1)))

[#9]

@Patrick2788Thank you for your work! That worked on A26 of the ShipDetForm, but did not provide the rest of the POs for a particular invoice. ... and I don't know enough to adapt it to do that. Do you?

[#10]

@ocatrina 

Which one is not working?

[#11]

Your formula populates the first PO of the invoice over and over. It does not bring in the other purchase orders of the invoice as I drag down rows A26:A44. I love your formula for its simplicity if you could make that happen. :)

[#12]

The formula I got to work would populate each row, as I dragged the formula over them, dependent upon if there were more PO#s to be had for the invoice, then left the rows blank when there were no more to be moved onto the ShipDetForm.