How to create formula for corresponding data across two sheets?

Question

The doc I am using is linked below. I am trying to set it up to copy information from the Sheet titled "Rates" to the Sheet titled "PO" based on info that is selected in the dropdown menu in cells C/D 19-29 on "PO".

https://docs.google.com/spreadsheets/d/1rzlEbp9fEwRwqtfGHrgLbtYqRfFM1FEtpzvugk6Wdok/edit?usp=sharing

I have created a dropdown list in columns C/D under "ITEM". The items in the list match the items in column B on the Sheet "Rates". I would like to set it up to copy the data from Columns A and C on "Rates" and populate it in columns B and E, respectively on "PO". So, for example, if I select "Bloody Mary Box - Front Label" in the dropdown in cell C/D 19 on "PO" (which corresponds with cell B2 on "Rates) I would like it to automatically populate the data from corresponding cells A2 and C2 on "Rates" into cells B19 and E19, respectively on "PO".

Can anyone help me create a formula to do that? Of course, I am happy to clarify if needed.

Answer 1

Place this in cell E19

=ARRAYFORMULA(if(ISBLANK(C19:C29),, VLOOKUP(C19:C29,Rates!B2:C24,2,False)))

It will look up the values from the Rates sheet and apply them, and do this for rows 19-29. If there is no value in column C it will return no value.

The Arrayformula and use of array ranges, C19:C29 applies this to every row in that range. The IF test the cell in column C. If it is blank, the test returns TRUE. Since we have ,, taking us past the IS TRUE portion of the IF statement, we return no value when cell C is empty. When cell C has a value, we look that up in the Rates sheet via VLOOKUP() and return the second column of the range we search. The FALSE for VLOOKUP means the data does not have to be sorted so it looks for an exact match as opposed to the first closest match.