1. R

    Sum based on looking up mutliple comma seperated values.

    Hi In Cell E2 below I have the formula =SUMPRODUCT(SUMIF($A$2:$A$5,MID(SUBSTITUTE(D2,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(D2,",","")))),4),$B$2:$B$5)) and it works a treat. However I need the same in G2 to J2 (the ?'s), where it pulls out the values based on the Heading in G1 to J2. Thanks...
  2. M

    Lookup, Vlookup, Xlookup???

    I have a file that keeps track of a dollar debit and converts to a Kč (Koruna or crown) amount based on the exchange rate of the day the dollar amount was subtracted from my dollar account. The row containing this data can be seen in the attached image. I began this record-keeping in January of...
  3. N

    Lookup using a single cell with concatenated values and return values to a single cell as concatenated values

    I have a situation where we are asking users to provide the name or names of groups that can be assigned to an underwriting condition. They are to enter all the names that apply separated by a comma into one cell. I have a another tab that has the names and the associated ID's. I need to...
  4. K

    Lookup Approximate match against multiple criteria!

    Hello Everyone, I have a data table and i want to find approximate match against that table, CATEGORY DENIM DENIM NON_DENIM NON_DENIM RANGE NORMAL WASH HEAVY WASH WASH DYE 999 8.00% 12.00% 8.00% 12.00% 2,999 6.00% 8.00% 6.00% 8.00%...
  5. J

    Look up a value for which 2 pre-conditions need to be met (ID and Year)

    How can I look-up the share (Column G) for the needed ID and the corresponding year? See attachement.
  6. R

    Fancy lookup required! :)

    Hi, I need a formula that can identify when the Employee in column A leaves the business. This can be seen from the -1 in the respective months. For example Employee 1 leaves the business in P03. This is populated below the data and this is where I'd like the formula to work from if possible...
  7. M

    Lookup across multiple columns

    Hello I'm new to the forum and look forward to your insights I have the weight band in column D and want to look up the prices in columns E to J how do I return the right price for each row? See below <tbody> Weight (lbs) Weight Band A B C D E F 167 A 156.53 126.79 107.98 90.78 78.94...
  8. S

    Lookup query

    Hi. In Sheet0 I have columns A to O populated with travel data. E contains email address I contains departure date L contains country B has the formula =IF(COUNTIFS(TA!C:C,E2,TA!E:E,"*"&L2&"*",TA!D:D,"<="&I2+7,TA!D:D,">="&I2-7)>0,"yes","no") which looks to see if there is a corresponding value...
  9. S

    Lookup text between 2 dates - is it possible?

    Hi, im trying to list/extract all the text from Z16:Z116 when another columns date falls between a start and end date. Z16:Z116 = text to extract AV16:AV116 = contains date A1 = start date C1 = end date is this possible?, I've tried Lookup & Index formula I have found on forums without...
  10. D

    #N/A error with Lookup and Min functions

    Good Afternoon everyone, I have run into an issue using Lookup with Min. I can use Lookup with Max and get the required results, =LOOKUP(MAX(E7:E1000),E7:E1000,D7:D1000) actually gives the date that matches the Max value. When I change the formula to =LOOKUP(MIN(E7:E1000),E7:E1000,D7:D1000), it...
  11. M

    Lookup Function

    will someone help me to convert the following formula in access. LOOKUP(2,1/((SEARCH($D$1&"*",BOM!$I$88:$I$2617))*(BOM!$C$88:$C$2617=Sheet6!A6)),BOM!$D$88:$D$2617),"") Thanks in advnce
  12. B

    Sumif using a list of criteria

    I was wondering if there was a formula that I could use to sum a data file using the below columns as the lookup, I would also like to use another criteria in addition to this. I.e If any of the numbers in column A appears in raw data file, sum the respective number in another column...
  13. Y

    Lookup between alphanumeric range?

    Hello, Probably being thick here and the obvious answer is alluding me but I need a formula to return a value based on where something is between a range of alphanumeric characters. At a basic level something like the below: <tbody> Start Location End Location Who A001 A100 MT A101 A200...
  14. C

    VLookup within a macro?

    Hi Excel geniuses, I'm looking for a macro which will effectively do a VLOOKUP, but where the lookup array is in the macro itself. Basically I have stand alone csv file, the users of which won't have access to a shared folder in which the lookup table can be stored. In one column I have a...
  15. O

    Vlookup with variable lookup value

    Hello, Is it possible for a VLOOKUP to have a multiple lookup value. For example: =VLOOKUP(A1,'Data'!$A$1:$D$1000,2,FALSE) If the lookup value in A1 is empty it can look at a different lookup value in A2? Thanks in advance, Chris
  16. J


    Hello, Can someone help me understand this array formula. I found it online. It returns the value in the last occupied cell in a row but I would like to understand how the middle section works. My understanding is that ($T14:$HK14<>"") is FALSE, so what does dividing 1 by FALSE work. If...
  17. A

    Populate cell value based on a lookup & match from two other tabs

    Hello, I am fairly novice to using excel, and have searched for my answer but am running low on time to find an answer, so figured I would just post to see if I can get a speedier answer. Below is an explanation of what I am trying to do. I have two tabs in a workbook with some of the same...
  18. N

    using vba or excel find the first reference match

    hi guys, i would like to ask how can i resolve this..... Below is my actual data. date time temp 1 10/7/2019 0:12 120.3 2 10/7/2019 0:13 119.5 3 10/7/2019 0:13 121.3 4 10/7/2019 0:13 120 5 10/7/2019 0:13 121.2 6...
  19. D

    Excel Lookup and SUMIF to data on Different Workbook **Help Please**

    Hi, I wonder if anyone could help as I have been wracking my brains. I have set up a fomula to lookup against data on a different tab on the SAME workbook. Example below - this lookups the tab number noted on cell D21 of the current sheet, finds the 2nd column (column B) against the lookup...
  20. Z

    cell value format

    Hello, i'm using the below table to track monthly sales, i inserted a slicer with the data in order to auto filter based on my selection, in the lookup column (index formula & slicer used) i cant or don't know how to get (Quantity-Amount or %) with its cell format, example: if i want to select...

Some videos you may like

This Week's Hot Topics