Index More Than One Reference

[Bedford]

Formulas from a workbook, sheet named “OEM INVOICE 1PG”. A formula in cell C28 is using INDEX to return data based on the result in cell AY6 from a sheet named "PO DATA" in column “D”. This is working fine. Cell M28 is indexing with =IF(ISNA referencing cell C28 to locate and return data from the sheet named "PRODUCT CODES" in column “Z”, this too is working fine.



Here is where it gets complicated, if data in C28 does not find a match in sheet named "PRODUCT CODES", I’m hoping to have M28 return a manual entry that matches the data in C28 from the sheet named "PO DATA" in column “M”. Therefore maybe an “ANDIF” type of function? Looking for help.



These existing formulas might explain better.

Formula from C28;

=IF(ISERROR(INDEX('PO DATA'!$M$6:$M$38,SMALL(IF('PO DATA'!$D$6:$D$38<>"",IF('PO DATA'!$D$6:$D$38=$AY$6,ROW('PO DATA'!$D$6:$D$38)-ROW('PO DATA'!$D$6)+1)),ROWS('PO DATA'!$D$6:D6)))),"",INDEX('PO DATA'!$M$6:$M$38,SMALL(IF('PO DATA'!$D$6:$D$38<>"",IF('PO DATA'!$D$6:$D$38=$AY$6,ROW('PO DATA'!$D$6:$D$38)-ROW('PO DATA'!$D$6)+1)),ROWS('PO DATA'!$D$6:D6))))



Formula from M28;

=IF(ISNA(INDEX('PRODUCT CODES'!$Z$5:$Z$1683,MATCH(C28,'PRODUCT CODES'!$A$5:$A$1683,0))),"",INDEX('PRODUCT CODES'!$Z$5:$Z$1683,MATCH(C28,'PRODUCT CODES'!$A$5:$A$1683,0)))

Thank you.
Doug

 

[DanteAmor]

Formula from M28;

Try this in cell M28:

=IFERROR(XLOOKUP(C28,'PRODUCT CODES'!A:A,'PRODUCT CODES'!Z:Z),XLOOKUP(C28,'PO DATA'!M:M,'PO DATA'!C:C))

’m hoping to have M28 return a manual entry that matches the data in C28 from the sheet named "PO DATA" in column “M”.

I didn't quite understand what you mean by "return a manua". But in the previous formula if it does not find in the sheet "PRODUCT CODES" then it looks in column M of the sheet "PO DATA" and returns the data from column C, just adjust column C by the column you want the data from.

 

[Bedford]

Try this in cell M28:

=IFERROR(XLOOKUP(C28,'PRODUCT CODES'!A:A,'PRODUCT CODES'!Z:Z),XLOOKUP(C28,'PO DATA'!M:M,'PO DATA'!C:C))

I didn't quite understand what you mean by "return a manua". But in the previous formula if it does not find in the sheet "PRODUCT CODES" then it looks in column M of the sheet "PO DATA" and returns the data from column C, just adjust column C by the column you want the data from.

Hello Dante, it seems you were on the right track. Your formula returned what I had requested, only my request was a bit incorrect. The description I gave had your formula return data from "PO DATA" in column "C", a quick adjustment here to have it return the correct data in column "N" and it works beautifully, thank you very much for your help in resolving this.
Doug.