V-lookup if statement excel

0
votes

To set the context: 2 Sheets, data below:

Sheet1:

Sheet1

Sheet2:

Sheet2

The goal is to extract the data from sheet2 and implement it into sheet1 via a V-Lookup if formula. The data also won't be an exact match (i.e. Dog to Dogs).

In other words: if the name of the animal in sheet 1 matches any of the names of animals in sheet2 then extract the data in sheet2 on average lifespan and implement it into column C in sheet1.

Once I figure out this formula it'll apply to a more complex sheet.

1
excelif-statementexcel-formulavlookup
I suggest you do a "fuzzy lookup". You can either download and install Microsoft's Fuzzy Lookup Add-In, or use a Fuzzy Join in Power Query (available in Windows Excel 2010+ and Office 365. – Ron Rosenfeld
Alternatively, sanitise your data, so that the lists do match – chris neilsen

1 Answers

0
votes

Try the following on C2 cell under Sheet 1. Then drag the formula to other cells.

C2=VLOOKUP(Sheet1!A2&"*",Sheet2!A:B,2,FALSE)

Note: It will search the string, like Cat* in Sheet 2 Column A. If it matches (Cats or Cata, or Catalog) it will return the value in Sheet 1 Column C2.