CODEWITHSUNDEEP
excelif-statementexcel-formulavlookup
Priyanka Patel
Priyanka Patel

Reputation: 3

Using If and Vlookup Statement together

I am trying to change the Master Key to a “new key” if the origin and destination match the old key.

Basically the master key from table 1 will look to see if the key exsists in Table 2, if it does it will pull the new Key. If the master key does not exist in Table 2 it will not change. It will be the original origin and destination. I have tried using if statements with vlookups however I am getting stumped with the logical error. Does anyone know how to return the new value or if there is another way to solve this?

I have attached an example. In the example, in table 1 the Master key is made from London and Italy together. This master key needs to be looked up in Table 2 (located in Column A) If it’s there it will pull the New Key (Column D).

enter image description here

Upvotes: 0

Views: 953

Answers (2)

mjohnson
mjohnson

Reputation: 58

I would use an Index/Match solution rather than a vLookup. vLookup is great for simple tasks, but it doesn't usually work whenever you try to do anything more complicated. I now find it is a simpler solution almost everytime (though I am not sure how it compares in performance).

Try something like this in your table1 cell D2:

=IFERROR(INDEX(Sheet2!$A$2:$F$4,MATCH(Sheet1!A2,Sheet2!$A2:$A4,0),4),Sheet1!A2)

What this will do is starting with the Match portion:

It will check the list in Sheet2 A2:A4 for a match of the same string as in Sheet1 cell A2. If it finds it, it will return a row number, if not it will return an error.

Next, the INDEX takes your second table as a source, and uses the row number determined in the Match and the fourth column (which is column D), and returns that value. If the Match returns an error, this will also return an error.

So, we use the IFERROR to capture the situation where the MasterKey in Table 1 does not exist in table 2. In that situation, I am assuming you just want to keep the same MasterKey from Table 1, but you can change that to anything you like.

If that works, you simply drag the formula down from Sheet1!D2 down to Sheet1!D4. Also, if your Table2 is much bigger then you have shown, you will need to adjust the Index and Match inputs to reference the correct range size. Keep in mind the use of "$" as they will prevent the formula from changing those cells when you drag it down.

Upvotes: 1

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Try this formula in D2 of Sheet1 and drag it down,

=IFERROR(VLOOKUP(A2,Sheet2!A:F,4,FALSE),A2)

Upvotes: 2

Related Questions