1. B

    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...
  2. L

    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...
  3. E

    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...
  4. D

    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...
  5. M

    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...
  6. T

    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...
  7. D

    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...
  8. A

    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...
  9. L

    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...
  10. F

    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
  11. N

    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...
  12. J

    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...
  13. D

    Formula to extract specific text from cell

    Hi everyone, I have a pretty puzzling task (hopefully not for you guys). I need to perform a vlookup between 2 sheets to see if an ID exists in the other sheet. My problem is, in Sheet A, the id is one Id for cell. for example: G-1234 G-1235 G-1236 G-1247 However Sheet B is a...
  14. A

    Formula required for work - Lookup Matrix to provide result in cell

    Hi All, I have this assignment at work, and i tried bunch of formulas but cannot get this (hopefully) easy formula right, i have tried INDEX, MATCH, COLUMN, COUNTIFS, SUMPRODUCT but all to no avail; i cannot comprehend the complexity and turn it into simple logic. In the image attached, i have...
  15. A

    Extract All lines cotaining the last distinct value.

    Hi, I am currently relatively new to excel and I have an issue. I want to extract from a sheet all the lines that contain the last distinct value of a column. So have something like this 534 16 14 534 64 16 020 24 22 020 16 14 020 64 16 134 24 0 134 8...
  16. R

    Fancy sumproduct / index lookup

    Hi, Apologies for not uploading a workbook - company restrictions prevent me from this However, I need to consolidate the employee data in table 2 at the very bottom and present it as per table 1 below this message. the main issue I'm encountering is that the pay category runs across the row...
  17. B

    VBA Lookup between two ranges

    Hoping I can get some help to speed up my code here I have a range of values in column 'B' starting at B6 in a sheet titled 'Bin Range' and each value in this range needs to be looked up against another value lines in column A starting in A1 on a sheet called 'Remove Bins' If there is a...
  18. A

    Identifying Items with the most in common

    I'm working on a file that can automatically identify products with the most in common. For example I've tagged products with 20+ attributes(ex. Price, color, shape, etc). I want to be able to identify a comparable item based on which OTHER product has the most attribute matches. So for...
  19. ncsushley

    Array formula with lookup based on multiple criteria including a date range

    I tried to find help with this question on Stack Overflow here but did not get a complete answer. I use this Google sheet to track proofreading jobs. I'm trying to modify the array formula in P1 that calculates the billable total for each job. The formula currently uses the Turnaround and...
  20. D

    Search two columns using one criteria return third column

    The results in column "N" are either in column "T" or "U" I need to be able to find the matching number in Column "T" or "U" and return what is in column "V" into column "K". If no results are found it needs to say "check lookup table" Index, Match will only look at one column, not sure what...

Some videos you may like

This Week's Hot Topics