CODEWITHSUNDEEP
excelexcel-formulavlookupexcel-2013worksheet-function
user2077480
user2077480

Reputation: 85

Combine OFFSET with VLOOKUP

I have two Worksheets in Excel 2013. In the first one I have Column A with Customer Numbers e.g. 999999 (starting in A2) and in the second one I have a table that matches the old Number Format to a new format. So I have a Column E with the new Format e.g. 1111111111 and in Column F the connected old Format 999999 (same as in the first worksheet).

I want to take the Number from Sheet 1 (Column A) and search it in worksheet 2 (Column F), if there's a match, I want to check if there exists a new Format for the matching Number in Column E, if so, take the new number, if not take the old one.

I tried this one, but it won't work:

=IF(OFFSET(COLUMN(VLOOKUP(A2;'[Konzern Kunde HKunde_neu.xlsm]Konzern Kunde'!$F$2:$G$456;1;FALSE))ROW(VLOOKUP(A2;'[Konzern Kunde HKunde_neu.xlsm]Konzern Kunde'!$F$2:$G$455;1;FALSE));0;-1)<>"";OFFSET(COLUMN(VLOOKUP(A2;'[Konzern Kunde HKunde_neu.xlsm]Konzern Kunde'!$F$2:$G$456;1;FALSE))ROW(VLOOKUP(A2;'[Konzern Kunde HKunde_neu.xlsm]Konzern Kunde'!$F$2:$G$455;1;FALSE));0;-1);VLOOKUP(A2;'[Konzern Kunde HKunde_neu.xlsm]Konzern Kunde'!$F$2:$G$5000;1;FALSE))

Upvotes: 2

Views: 1698

Answers (2)

user2077480
user2077480

Reputation: 85

oh wow that was easy. Thank you. It works like a charm :) And yes I forgot to replace the ";" with "," because of the european region. Sorry for that.

Here is my complete solution.

(German Version) =WENN(ISTNV(SVERWEIS(A2;'Sheet2'!$F$2:$G$5000;1;FALSCH));SVERWEIS(A2;'Sheet2'!$E$2:$G$5000;1;FALSCH);WENN(INDEX('Sheet2'!$E$2:$E$5000;VERGLEICH(A2;'Sheet2'!$F$2:$F$5000;0))=0;SVERWEIS(A2;'Sheet2'!$F$2:$G$5000;1;FALSCH);INDEX('Sheet2'!$E$2:$E$5000;VERGLEICH(A2;'Sheet2'!$F$2:$F$5000;0))))

(English Version) =IF(ISNA(VLOOKUP(A2,'Sheet2'!$F$2:$G$5000,1,FALSE)),VLOOKUP(A2,'Sheet2'!$E$2:$G$5000,1,FALSE),IF(INDEX('Sheet2'!$E$2:$E$5000,MATCH(A2,'Sheet2'!$F$2:$F$5000,0))=0,VLOOKUP(A2,'Sheet2'!$F$2:$G$5000,1,FALSE),INDEX('Sheet2'!$E$2:$E$5000,MATCH(A2,'Sheet2'!$F$2:$F$5000,0))))

Short explanation again what this is doing (see Task above for structure of excel sheets): It takes a value from "A2" in sheet 1 and searches it in the matrix "F2:G5000" in sheet 2.

If value is NOT found, it takes the value from "A2" in sheet 1 and searches it in the matrix "E2:G5000" in sheet2.

If value IS found, it returns the row of the match and takes the value of the row in the matrix E2:E5000. But if the value of the taken row from E2:E5000 is 0, it goes back to the value in F2:F5000 in given row.

Hope it's correct explained and understandable.

Thanks again.

Greets Domi

Upvotes: 0

pnuts
pnuts

Reputation: 59485

Please try in Row2: 

=IFERROR(INDEX(Sheet2!E:E;MATCH(A2,Sheet2!F:F;0));A2)

copied down and adjusting your sheet names to suit.

Upvotes: 1

Related Questions