=XLOOKUP(D107,Prices!$A:$A,Prices!$E:$E)=IF((Xlooup,D107,Prices!$A:$A,Prices!$C:$C,>200,MULTIPLY(10)))
This commands goal is display a number from sheet A by referance multply this by ten if thier is less than 200 Sheet A ref diffrent columm
What i need this to do is display prices from sheet A but modif them based on another look up from sheet A and if possible do this in one step.
Nested VLookup - Google Docs Editors Community
Nested Lookup Formula Across Multiple Excel Sheets - Google Docs Editors Community
Is it possible to have an XLOOKUP nested inside of an IF formula in a workbook referencing multiple sheets? I'm trying to prevent formulas from needing updates if the sheet they are referencing gets reformatted.
Dropdown lookup using multiple headers, nested XLOOKUP and possibly SWITCH - Google Docs Editors Community
Does Google Sheets have XLOOKUP?
What is the XLOOKUP syntax in Google Sheets?
Can XLOOKUP pull data from another spreadsheet?
I would like cell B4 on "Purchasing" to return the total number of components needed to be ordered for that production week, based on the data in "Schedule" & "Components." (manual math at the bottom).
I am currently using a mess of IF formulas (not shown) to do the math with direct cell references, but when the components list needs to change, those formulas also need to be updated. I'd like the formula to be smart enough to search the Components sheet and cross it with the Schedule and still return the correct results, even if the list order of components changes.
XLOOKUP I think is ideal so that if the component list moves around (as it often can) the formula will still return the correct information instead of referencing the wrong component. Or is there another way to ensure that if the components list order changes that it will still pull the correct info without needing to update the formula?
I have been scratching my head for hours on how to blend an XLookup into an IF formula. Please let me know if I am overcomplicating this with that idea and it's not possible?
Google Sheets screenshots of example sheet shown for ease, but I am doing the real math in Excel
Manual math -
What is the total amount of Red components needed for production Week 1?
During full production runs (100%) Bicycles need 39 Red, Jump Ropes need 11 Red, Rollerskates need 18 Red.
Week 1 - Production will be 50% Bicycles, 25% Jump Ropes, 25% Rollerskates.
So -
(50%*39)+(25%*11)+(25%*18)
= 26.75 Red components needed for production week 1.
I'll try and long hand the logic in case that helps?
Look for Red in "Components" sheet
Look for Week 1 in "Schedule" sheet
Look for Products (Bicycles, Rollerskates, Jump Ropes) in "Components" sheet
Add total values of Red components needed for each product.
Multiply by the percentages of that week's production run.
Return value
You may use trim function to remove extra space.
=XLOOKUP(A2,Sheet2!$A$2:$A$5,XLOOKUP(B2,TRIM(Sheet2!$B$1:$E$1),Sheet2!$B$2:$E$5))
Hi,
Remove the extra spaces (trailing and leading) which appear in range B1:E1 of sheet2 and enter this formula in cell C2 of sheet1
=XLOOKUP(A2,Sheet2!$A$2:$A$5,XLOOKUP(Sheet1!B2,Sheet2!$B$1:$E$1,Sheet2!$B$2:$E$5))
Hope this helps.
Hello! am new here and need some help regarding the xlookup function in google sheets,I am wondering if I am able to have more than one search key or a range of search keys for the xlookup, so that they return the corresponding values and then i can sum it, i tried doing using the function as shown but to no avail.
an e.g of what i wanna achieve would be to sum the salaries of the individuals automatically based on what name i keyed into the cells above the total
Thanks if advance!