How to use VLOOKUP function

I intend to generate revenue results with two separate records, one with a customer transaction and another with a product list

How I do: I create a new table that has all customers name. Search for the customer name in each report and add corresponding value like transaction made by that customer and which product customer has purchased.

How can I do it better: You can use the VLOOKUP formula throughout the table to find transaction amount and product of each customer. This will not only reduce your manual search work but will also highlight Customers who have done transaction but do not have any product details. Click one of the following options according to your software preferences:

When finding items in a table or in a range by row, use VLOOKUP. For instance, check the price of a product in your sales report or find an customer's name based on email ID.

Microsoft Excel or Google Spreadsheet Pro Tip: Reference Table : Transaction Report Row- A Row- B 1 Name Amount 2 John 20 3 Harry 10 4 Michal 26 5 Tom 50 6 John K 45 Actual Table: Row- D Row- E Look up value Amount John K Find value??? =VLOOKUP(Reference Value, Array of the table that has return value, column number of the return value, True(Approximate match)/False(Exact Match) STEP 1: Add reference value in a cell. Type the following formula and add atleast three parameters. Select the reference cell as first parameter: =VLOOKUP(Select the Reference Cell Value) From above example: =VLOOKUP(D1) Note: Type ","(comma) after each parameter. The VLOOKUP formula will only return value if it is a single, non repeated search value. STEP 2: Enter the following arguments: Second Parameter: Select the Table which includes value that you want to return. Third Parameter: Type Column Number of your return value You can also type the following:=VLOOKUP(Select the value to search for, Table, index number of Column that has return value,) Example 1: =VLOOKUP(D1,A:B,2) Example 2: Now, if you have values in another sheets you can type the following:=VLOOKUP(D1,Sheet Name!A:B,2,) STEP 3: (Optional) Enter one of the following as forth parameter TRUE: To find the Approximate match. It means if your table has two values with minimum difference, the formula will return the correspoding value of first matched instance FALSE: To return value corresponding to exact match Example: =VLOOKUP(D1,A:B,2, True) In this case, if you are searching for corresponding value of "John K", the formula will return 20 instead of 45 as the first instance matched was "John" and not "John K" But, if you write forth parameter as FALSE i.e. =VLOOKUP(D1,Sheet Name!A:B,2,FALSE), the formula will return value after matching the whole string Once you set whole formula, you can drag (+) icon the formula across the rows to find all corresponding value of each reference cell Result: Look up value John K 45

ABOUT: When finding items in a table or in a range by row, use LOOKUPVALUE. For instance, populate the price of a product in your sales report or get an customer's name based on email ID.

Power BI Pro Tip: Example: Reference Table: Date Name Sales Amount 10-Jun-2020 John 20 11-Jun-2020 John 10 10-Jun-2020 Harry 15 11-Jun-2020 Harry 10 12-Jun-2020 Harry 25 Lookup Table: Date Name 10-Jun-2020 John 10-Jun-2020 Harry STEP 1: Go to Table, and click Table Tool > New Column. STEP 2: You must type the formula in following format: Lookup value = LOOKUPVALUE('Reference Table'[Sales Amount],'Reference Table'[Name],'Lookup Table'[Name]) STEP 3: In POWER BI, you also have option to return value based on two matches. You must type the formula in following format: Lookup value = LOOKUPVALUE('Reference Table'[Sales Amount],'Reference Table'[Name],'Lookup Table'[Name],'Reference Table'[Date],'Lookup Table'[Date] ) Result: Lookup Table: Date Name Total 10-Jun-2020 John 20 10-Jun-2020 Harry 15