Search and replace using VLOOKUP in Excel

1
votes

I have a workbook with multiple sheets and I need data from one sheet moved to another sheet using a lookup. I am having trouble getting VLOOKUP to work.

Sheet with the data in it is called - Sales Split Out
Sheet where I need the date replaced is called – Orders Paid
The unique number common to both sheets is called – SKU or Column B and Column G.

I need the Data from SHEET Sales Split Out Column O or Replace Product input into SHEET Orders Paid Column M or Replace Product. There will always be an exact match as no sale can be made without using a SKU.

Sample of Sheet – Sales Split Out

Sample of Sheet – Sales Split Out

Sample of Sheet – Orders Paid

enter image description here

Here are some of the formulas I have tried, this by far not the complete list

 =VLOOKUP(G2, 'Sales Split Out!'$B:$O, Exact Match)
 =VLOOKUP(G2,Sales Split Out! $B:$O,2,FALSE)
 =VLOOKUP(G2,Sales Split Out SKU:Replace Product)
 =VLOOKUP(G2 , Sales Split Out $B, $M, Exact Match)
1
excelexcel-formulavlookup

1 Answers

0
votes

Try,

'basic lookup
=VLOOKUP(G2, 'Sales Split Out'!B:O, 14, FALSE)
'error controlled
=IFERROR(VLOOKUP(G2, 'Sales Split Out'!B:O, 14, FALSE), "not found")
  • The Sales Split Out worksheet has spaces in its name so the name has to be wrapped in ' marks.
  • VLOOKUP always looks up in the first column so with 'Sales Split Out'!B:O, it looks up G2 in the first column, 'Sales Split Out'!B:B.
  • 'Sales Split Out'!O:O is the 14th column within 'Sales Split Out'!B:O so the third parameter is 14.
  • FALSE is the correct 4th parameter for an exact match on unsorted data so 'Orders Paid'!G2 has to exactly match something in 'Sales Split Out'!B:B in order to return something from 'Sales Split Out'!O:O.