Combining If/or and vlookup across multiple sheets
I have 4 sheets: NCR Summary sheet. Supplier A sheet, Supplier B sheet, and Supplier C sheet.
I want to pull information from individual supplier sheets to populate fields in the summary sheet. The summary sheet will already have the supplier name in a column and a unique identifier for each line in a column(NCR #).
I'm trying to build logic in a cell in the summary sheet that will use the Supplier name to know which sheet to reference and a vlookup to pull the specific data. i.e. Using the first line in the summary sheet shown below...To populate "Rejection Reason", the Supplier is "A", so go to sheet "Supplier A" and the NCR Number is "1", so return the value for row with NCR "1."
I can get a vlookup to work if I point the Rejection Reason cell specifically at the Supplier A sheet and return the value in the matching NCR row.
I cannot get it to use logic to determine which sheet reference to follow based on the value in the Supplier Name column.
I think I need something along the lines of:
=IF(OR(Supplier@row=A, (VLOOKUP([NCR Number]@row, {Supplier A Range 1}, 4)),"no data")), (OR(Supplier@row=B, (VLOOKUP([NCR Number]@row, {Supplier B Range 1}, 4)),"no data")),(OR(Supplier@row=C, (VLOOKUP([NCR Number]@row, {Supplier C Range 1}, 4)),"no data"))
Summary Sheet
One of the Supplier Sheets
Best Answers
-
You don't need the OR function(s). It would just be a nested IF.
=IF(Supplier@row = "A", VLOOKUP(supplier a sheet), IF(Supplier@row = "B", VLOOKUP(supplier b sheet), VLOOKUP(supplier c sheet)))
-
Hello @Jim McWilliams,
Instead of using the OR functions, I would suggest using sequential IF functions. Try this:
=IF(Supplier@row="A", VLOOKUP([NCR Number]@row, {Supplier A Range 1}, 4), IF(Supplier@row="B", VLOOKUP([NCR Number]@row, {Supplier B Range 1}, 4), IF(Supplier@row="C", VLOOKUP([NCR Number]@row, {Supplier C Range 1}, 4),"no data")))
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
Answers
-
You don't need the OR function(s). It would just be a nested IF.
=IF(Supplier@row = "A", VLOOKUP(supplier a sheet), IF(Supplier@row = "B", VLOOKUP(supplier b sheet), VLOOKUP(supplier c sheet)))
-
Hello @Jim McWilliams,
Instead of using the OR functions, I would suggest using sequential IF functions. Try this:
=IF(Supplier@row="A", VLOOKUP([NCR Number]@row, {Supplier A Range 1}, 4), IF(Supplier@row="B", VLOOKUP([NCR Number]@row, {Supplier B Range 1}, 4), IF(Supplier@row="C", VLOOKUP([NCR Number]@row, {Supplier C Range 1}, 4),"no data")))
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
Monique and Paul,
Thank you both for the suggestion of using sequential IF statements. That worked!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!