Forum Discussion
Need Formula for inputing data from another sheet into a cell, based on data in many cells.
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
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
Riny_van_Eekelen, this is PERFECT!!! And, thank you for the link to the XLOOKUP. That will definitely help in the future. You are just a fountain of information. And, you're so quick at responding to me. Thank you, thank you, thank you!!!
CindyMS You're welcome!