I have on Sheet1 18 columns:
N a list where an Order Number can be selected
O a Line number (as the orders have multiple line numbers associated with them)
P, Q and R, I want to pull over the data associated with the Order Number and Line number entered. The data it is pulling from will be on another workbook but I've copied it into another sheet (Sheet3) to try to get it to work.
My formula looks like this:
=Index(Sheet3!$A:$E,MATCH([@[Order Number]],Sheet1!$N:$E,0),3)
I want it to look at Sheet3 and say "I see this Order Number and this Line number, here is what I have for that combination in Columns C, D and E of Sheet3, place these in columns P, Q and R on Sheet1 respectively".
I did find one answer in here that is extremely close to what I am looking for but it doesn't work for me either:
=Index(Sheet3!$A:$E,MATCH(N5 & "|" & O5,Sheet3!$A:$E & "|" & Sheet3!A:E,0))
The "near miss" looks as though it used a helper column. Insert a ColumnC into Sheet3 and in C1 there enter:
=A1&"|"&B1
Copy that down to suit. In your other sheet in ColumnP (Row1 ? - or adjust the N and O row references to suit) enter:
=VLOOKUP($N1&"|"&$O1,Sheet3!$C:$F,2,0)
and copy across to R1. Adjust Q1 from ,2, to ,3, and R1 from ,2, to ,4, then copy down to suit.