lookup

1. Need help DAX LOOKUP formula which would work like VLOOKUP

Hello Everyone, I have two tables Table 1:- Voucher No. Invoice No. Invoice Amount Invoice Date Vendor Name 1 ABC-1 100 14/05/2019 ABC Company 1 ABC-2 200 14/05/2019 ABC Company 2 EFG-1 300 31/05/2019 EFG Company 2 EFG-2 400 31/05/2019 EFG Company 3 XYZ-1 500 30/06/2019 XYZ...
2. Suppressing zeros

A frequent question on the forums is "how do I not display a zero?" There are several methods to do so, each with their own pluses and minuses. I will explain the various methods here as a guide, and the interested user can choose the one most suited to their situation. Consider this...
3. Multiple match criteria in different columns to return the unique value

Hi, Can somebody help me with this problem? Thanks, Bobby
4. Lookup information from another sheet using VBA

Hi All, I have a list of information in sheet 1, like the following table Country Month Subject Item and in sheet 2, I created a layout with a drop list for Country and Month attributes I want a VBA code that will retrieve/lookup the subject and item based on select...
5. Find the REF No where the Amt is = to the same negative amount

I need a formula to show the REF No to be inserted in Result Column when the Amt is = to the Negative Amt as shown
6. Best approach for top sales deals?

Hi - Thank you in advance for the help. I'm working on a sales runbook that combines actual and pipeline data to get to a forecast for the quarter. The sales leaders need to be able to determine, at a specific opportunity level, if an opportunity is going to close this quarter or not. If it...
7. Find closest values

Hi, I'm new to macros and can't find a good starting point for my problem. I want to loop through a row in a table looking for the 3 closest numbers (column D) to a value in a given cell (N5) and set the corresponding row to "yes" in column B. So if the 3 closest values is found in D3, D6 and...
8. Index/Match returning first non-blank cell

So I understand index/match will return the first instance of the match it finds, even if the answer is "". What I need is a formula that will return the first instance where the cell is not "". Here's an example: Customer No. Salesperson Code C000176 C000176 DG C000176 DG C000176 DG...
9. Partial match when look up value is lengthier than corresponding value in a table or a range

I have table as below. In first column name of the company and in next column corresponding manager Name. Company Name Manager Name Amcor Amol Thermax Sagar The look up value of company name is not exact match in this case. e.g. It is "Thermax Ltd" The company name from table is a...
10. List default based on users information

Can I have some help please? (See attached image) I have 3 tabs. the first sheet has employee’s data. UserID, Name and Level Second sheet is where I want the help. There sheet shows the users name and level, the level cell is a dropdown list containing all the possible levels. (The list values...
11. Index Match Formula Shows Wrong Result

I have two table, this one is the initial table that contains raw data (on Sheet 2) ABCDEF1NoUserDateTimeActivityDescription2A-1Staff 305/06/20209:00testtest3A-1Staff 305/06/20209:30testtest4A-1Staff 305/06/202010:00testtest5A-1Staff 305/06/202010:30testtest6A-3Staff...
12. Extract all rows from a range that have specific text from lookup

Hi all, I have an excel file with multiple rows and columns used for planning jobs. The columns contain job descriptions, job numbers, Worker names etc. Few jobs require 2 people to complete and have 2 names on them ex: Luke and Mike. Out of all the jobs i want to extract the jobs for specific...
13. VBA - Lookup Entire Row from List

Hello, I am using VBA to create a user entry form where users select certain account numbers from a series of data validation dropdowns (this is already done and functions properly). The workbook also contains a second sheet with all accounts the user could choose from and their attributes...
14. Lookup with changing header columns

Hi, any suggestions would be greatly appreciated. Below are two example files I receive from a vendor. The file is usually massive but for this purpose, I'm showing simple tables below. The old formula I used was INDEX/MATCH to pull in Mary's phone to a different spreadsheet called MAIN. On...
15. IF function comparison

Hi, In the file attached, there is the Group column (B) and Name column (C) both of which are related to each other. I need a formula that would return a value under certain conditions of what is inside columns B and C. If e.g. the column B value is USA, but in column C we have "wood and...
16. Salary Table Lookup

I have a salary table that I would like to lookup a grade and step and find the corresponding annual, hourly, and overtime rate. I have tried a vlookup and index combo but can't get the correct returns. Any help is appreciated. Below is what I have so far...
17. How to return the starting balance of a bank account in a master list of bank accounts

Hi everyone, I've built a workbook that evaluates a client's banking information and processes multiple bank accounts at a given time. I'd like to find a formula that will return the starting balance of each unique bank account from my dataset to reduce human error with data input. The...
18. Sum by Year Index Problem

Hi, I want to sum a data table by year. How do I do that? 'Summary' tab - lookup Product A in the 'data' tab and sum the monthly expenses of Product A by year. https://docs.google.com/spreadsheets/d/1N76bSX4PbzdknOg3qbGBU2i5t6sD2Ld1LHJxzhD_SzM/edit?usp=sharing
19. Lookup Formula/VBA to find correct duplicate

Hi there, I am looking for a formula/VBA solution that will lookup an ID in a separate list, then, if the ID appears in the list more than once, I want the formula to look at the date in the first list and find the ID in the 2nd list with the closest date match before returning the value in...
20. Vlookup error

I have a vlookup in one spread to look up qty's in another and is erroring #N/A. when I go to the lookup table and click in the cell and hit F2 and enter the error goes away. so I then try to copy this down and I have to go cell by cell hitting F2 enter. not changing anything. is there some...

This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...