excel & lookup

  1. 4

    Lookup 2nd Occurrence of Value Starting From Bottom Row And Return Value In Different Column

    I'm looking for a formula that will look in Column A:A and match the 2nd occurrence starting from the bottom row and looking up, looking for a value located in C1. It will then need to return the value in the same row as the 2nd occurrence in Column B. For instance: Row/Column A B C 1 4 E...
  2. D

    Find the latest cost of an item based on sales date

    Please help. Two worksheets: item_cost and item_sales. item_cost has items and cost with dates. ABCD1cost_dateitem_namecategorycost21/1/2021Body armorSoda$ 2.0031/1/2021dasani 20 ozWater$ 1.2541/1/2021Dunkin donuts iced coffeeIced tea or coffee$...
  3. J

    Excel - Wildcard match with multiple criteria?

    I have a database spreadsheet where I need to pull key information from the master data. As you can see in the below screenshots, the goal is to return the "Part Number" and the only data I can match with are the "Kenn" and the "Part Code", "Kenn" can be directly matched with "KENN No" from the...
  4. K

    How to match same invoce no. & get data "FILE -1" TO "FILE -2" ...

  5. H

    Converting a number into particular format

    Hello, What is the best way to convert following format into e.164 in excel i.e. i would like to convert numbers in the format 123 456-0001 to \+11234560001. Column A Column B 123 456-0001 \+11234560001 123 456-0005 \+11234560005 123 456-1000 \+11234561000
  6. H

    Excel vlookup with variable information

    Helloooo, Is there a way please to lookup information from other spreadsheets based on variable information? This is quite hard to explain so I’m just going to list out what I have below and hope that someone can make sense of what I am trying to ask! So this is a little mock up of my scenario...
  7. U


    Hello, Would you know when would all Excel, Office 365 users will be able to utilize the following formula: "XLOOKUP"
  8. V

    Calculate a cell value based on a specific string in a separate cell

    Hello, The first column contains a string of values as mentioned below . In 2nd column, I need to strip out specific info from Col 1 (as mentioned below). I tried using a formula like this - =LOOKUP(MID(AS2,12,3),{"Lar","X-L"," Sma"},{"Large","X-Large","Small"}) , but its not calculating the...
  9. G

    Formula/Macro Help

    <tbody> A A B 3 C M D E GENBPAPP A 3 S GENBPAMIN A 1 SE SEBPA B 1 M GENLPA B 1 A GENLPA A 3 S CU CUAMIN </tbody> Hello, I have the above table (which I hope the format sticks around) as a lookup. I have rows of data that contain values. am looking for a formula or vb logic...
  10. C

    Selecting rows that best fill a specified criteria

    I have a worksheet that lists necessary components to meet an objective and then several source inventories. I would like a way to pick the combination that best fits the needs and the combination with the fewest sources that meets the needs. <tbody> <colgroup><col><col...
  11. E

    Excel Formula Help - Thanks in advance

    Hello, I was wondering if the good natured people here at Mr Excel can maybe tell me what im trying to do is feasible or not, Basically, I have a few different formulas connected to a file on Sharepoint (just index match and vlookup) and they all work fine. here is one of them below...
  12. A

    Identify Range based on set of values

    Hi, I have set of values in column A, for e.g. 10,20,30,40,50,60,70,80,90 and 100. Now in column B I have below values 33,50,60,90 How to identify, range of values in column A based on values in column B. It should return following values : 30,40,50,60,70,80 and 90(assuming we are taking...
  13. P

    Exceptions formula

    I have a data set where I am trying to create a formula for each work order that does not have a specific task ("Task 5" Below Table) within the work order based on specific customers that must have this task with their work. There will be customers that do not apply and will not need this task...
  14. E

    How to loop though a range of cells and if it is false to skip to search the next row, rather than insert my false result

    Hello there experts, I have the following code that successfully returns the values of any cells in column A starting with a letter A to Z: =IF(AND(CODE(UPPER('PL & BS Leadsheets'!A5))>64,CODE(UPPER('PL & BS Leadsheets'!A5))<91),'PL & BS Leadsheets'!A5,0) The range in sheet 'PL & BS...
  15. A

    Index Match to replace a Vlookup

    Good day, I have a table that stores rates for NI Contributions called Rates and I have been using a Vlookup to return the Rate based on the employee Salary like this VLOOKUP(I4,'NIS AND TAX TABLE'!$E$2:$F$18,2) Now that the rates have been increased (highlighted in the Rates Table...
  16. S

    Formula to Match Text and update for same values in the row

    Hello Team, Need to look for "AA" in the Text and if so update the Yes in result , for those multiple invoices if the Text is also not equal to "AA". i have trying with below formula, but stuck: =IF(AD16="AA","Yes",IF(MATCH(J16,$J$16:$J$22,0),"Yes","No"))...
  17. N

    V look up substitute for data with multiple arguments that fall within certain timelines

    Hey there, I was wondering if somebody could help me. I have a time card data excel sheet for all restaurant workers that has their clock in & clock out time, their job title and their total hours for one particular day. For example: Time Card Sheet (Sheet 1) <tbody> Department Clock In...
  18. A

    Can someone explain how the below formula to transpose all rows to one column works?

    Hello, I have used this formula: **=INDEX( MyData,INT((ROW()-ROW( $Z$1 ))/COLUMNS( MyData ))+1,MOD(ROW()-ROW( $Z$1 ),COLUMNS( MyData ))+1)** to transpose all of my rows of data into one column. Unfortunately, I can't entirely wrap my head around how it works. Would someone be willing to...
  19. U

    VBA vlookup to Reference Sheet & Create a Pivot

    Hi Guys, I have a massive data set that needs vlook up and pivot. I have this excel file named "MasterData" with two tabs sheets namely: Master & Reference. The master contains a list of data while reference sheet has list of countries and its equivalent country group. Example Reference...
  20. F

    Match Stock Prices with Stock Splits VBA

    Greetings (first post and I am in need of help)! I am writing some VBA and here is what I would like to do but I am having difficulty creating the structure. I have a list of daily stock prices for multiple companies, although the stock prices do not indicate that they went through a stock...

Some videos you may like

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back