Forum Discussion
Do I need to write an IF Then function with Vlookup nested?
Hello, I am trying to update the cost column by pulling the new cost from another work book. I had tried to use a VLOOKUP function but not all skus in column A appear in the other sheet. My question is how would I write and If then function with VLOOKUP nested to pull the price when the skus match and leave it as it is if it doesn't appear on the other workbook? I am currently working on windows 10. Thanks!
3 Replies
Hi Luke_Hively
just combine a VLOOKUP with the IFERROR-function.
Let's assume, the list with new cost prices are in columns I and K.
You would add a new column E to your old list to find the new cost price, using this formula:
=IFERROR(VLOOKUP(A2;$I$2:$J$3;2;FALSE);D2)
So if VLOOKUP returns an error, which means, the SKU is not on the new list, then it just takes the old price from column D
Hello Martin_Weiss - I also need similar assistance.
I need to the criteria from Sheet 2 columns A & B to match Sheet 1 with return data from column H
Sheet 1
Sheet 2
Hi AMCGWIER
according to your screenshots a assume you need to match columns B & C from sheet 1 with columns F & G from sheet 2, right?
There are at least two options.
Because VLOOKUP can usually handle only one criteria, you need to use a more complex array formula:
{=VLOOKUP(B4&C4,CHOOSE({1\2},Sheet2!$F$1:$F$1000&Sheet2!$G$1:$G$1000,Sheet2!$H$1:$H$1000),2,FALSE)}
Please note, that you must not enter the curly brackets around the formula manually. Instead, enter the formula without curly brackets and confirm it with the key combination CTRL+Shift+Enter. This will convert it an an array formula and add the curly brackets automatically.
(If you use Microsoft 365/Office 365, you could enter this formula without any special key combination)
The second option would be helper columns in both tables, which combine the two fields:
And then you could use just a regular VLOOKUP:
=VLOOKUP(A4,Sheet2!$E$1:$H$1000,4,FALSE)
