Forum Discussion
Need Formula for inputing data from another sheet into a cell, based on data in many cells.
Hi, all! I am trying to help our son use excel to generate quotes for his side auto mechanic business. Within the workbook, there is a sheet for quotes and a sheet for the cost of the parts he uses. We need to be able to bring the prices from the second sheet onto the first sheet, based on the part that populates a cell on the first sheet. The second sheet is named Parts #. From my research, I was able to get it to work with just one value by typing in =IF(I12=('Parts #'!C2),'Parts #'!D2). However, if I type in the rest of the formula to give it the option of 7 difference parts and their corresponding cost, it no longer works.
This is the rest of the formula I used. What am I doing wrong? Thank you!!!
IF(I12=('Parts #!C3),'Parts #!D3),IF(I12=('Parts #!C4),'Parts #!D4),IF(I12=('Parts #!C5),'Parts #!D5),IF(I12=('Parts #!C6),'Parts #!D6),IF(I12=('Parts #!C7),'Parts #!D7),IF(I12=('Parts #!C8),'Parts #!D8)))))))
You can still use VLOOKUP, for instance like this:
=VLOOKUP(A1,'Sheet Name'!B1:H8,4,FALSE)In this example, B1:H8 is the range you want to pull the values from, where the left-most column in the range (column B), must hold a matching "lookup value" of what you have in A1. Then the number 4 means that you want to pull the value in the 4th column, starting from B (i.e. column E). If you want to to pull the value from column H, for instance, you put the number 7 here. FALSE indicate you want an exact match. If the formula doesn't find a match it returns #NA!.
In case you are on a recent version of Excel, you may want to have a look at the new XLOOKUP function as well. Read more about both functions in the link below:
https://support.office.com/en-us/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1
5 Replies
CindyMS Assuming that you copied the formula exactly as you have it in Excel, the first error is that you forgot to put the closing single-quote after each sheet name. Thus 'Parts #' .
Then, you had too many brackets in your formula.
IF(a=(b), c), IF(d=(e), f), IF(......etc. The correct syntax is:
IF(a=b, c, IF(d=e, f, IF(......etc.
Re-wrote your own formula and it produces zero as I don't have you data to test with. But no error at least.
=IF(I12='Parts #'!C3,'Parts #'!D3,IF(I12='Parts #'!C4,'Parts #'!D4,IF(I12='Parts #'!C5,'Parts #'!D5,IF(I12='Parts #'!C6,'Parts #'!D6,IF(I12='Parts #'!C7,'Parts #'!D7,IF(I12='Parts #'!C8,'Parts #'!D8))))))BUT.... why not try this one in stead. I believe it does the same:
=VLOOKUP(I12,'Parts #'!C3:D8,2,FALSE)Riny_van_Eekelen, thank you, very much! It worked great. Even better, the short formula worked as well!
A follow-up question for you: The second set of data I need to input has 2 columns in between the sets, so the shorter formula doesn't work. I'm pulling the data from columns C and F this time. How can I change the shorter formula to work in this situation?
THANK YOU!!!
You can still use VLOOKUP, for instance like this:
=VLOOKUP(A1,'Sheet Name'!B1:H8,4,FALSE)In this example, B1:H8 is the range you want to pull the values from, where the left-most column in the range (column B), must hold a matching "lookup value" of what you have in A1. Then the number 4 means that you want to pull the value in the 4th column, starting from B (i.e. column E). If you want to to pull the value from column H, for instance, you put the number 7 here. FALSE indicate you want an exact match. If the formula doesn't find a match it returns #NA!.
In case you are on a recent version of Excel, you may want to have a look at the new XLOOKUP function as well. Read more about both functions in the link below:
https://support.office.com/en-us/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1
