hlookup

  1. S

    hlookup automatically change 1st line in the table array

    Hi My formula is =HLOOKUP($D48,Directorate!D$52:D$350,3,FALSE) As you are aware for this HLOOKUP formula to work the infofrom Cell D48 must be on the line D52 (in the table array source), to bring back the 3rdrow values. However I would like to drag this formula this down (as Ihave 600...
  2. D

    hlookup and offset

    I'm trying to combine hlookup() and offset() to find the last value in a row, then go up 2 rows and left 1 cell. Each works independently, but when I try to combine them, errors occur. =OFFSET(M3,-2,-1) --- "M3" is the below hlookup() reference =HLOOKUP(9.99999999999999E+307,B3:Y3,1) Is...
  3. X

    Looking for help with a lookup. Happy 4th!

    Okay, this is probably something simple but I cannot figureit out. I have a single column of data – approximately 9,000 cells in length. Itfollows the same format. A person’s name followed by 3 values. Can I use alookup function that will return the same 3 values that follow each person’sname...
  4. T

    Hlookup and push out based on Payment Terms

    Hey guys Got a little trickier one. What I have is a weekly cash flow statement that I am attempting to project when we will receive cash payments on jobs invoiced. I have two tabs: the main CF tab and the invoice tab (see below). What I want to be able to do is for a given week, is to push out...
  5. U

    getting information relative to indirect source

    I have a problem that seems like it should have a simple solution. I'll use this example situation to explain. in the first table I have data validation drop-down lists in the first column to choose a category. In the second column i have an =INDIRECT drop-down list to get an Item out of that...
  6. K

    Hlookup, offset, sum

    Hello all! It's my first post, so please be nice :-) I'm trying to create a "dashboard" which looks up a value (hlookup) in the top row of the data set and returns back a sum of its "scores" from the column to the right. I tried and failed at nesting hlookup, sumproduct, and wrapping them in an...
  7. N

    HLOOKUP with Comments

    Hello, I am new to the forum as a registered user. Can you help me with a source code or something in vba? What I need is to gather data with HLOOKUP and comments from another workbook and put them altogether in another. I need this because the initial file is in the server and is being...
  8. A

    Looking for help with hlookup and concatenate or index match (newbie)

    I have a data sheet like the below and i want to for each row find the corresponding X and put it into the the below. I'm not savy with VB and my work computer has Excel 2016. Thanks for the help. <colgroup><col style="width:48pt" width="64" span="6"> </colgroup><tbody>...
  9. L

    Named Range in a Hlookup using Vlookup

    Hi Everyone, I'm trying to pull a Named Range using Vlookup into a Hlookup to return a text value. I've tested the individual parts and they work fine but when i put them together I get "#VALUE" as a result. The formula is: =HLOOKUP(E$1,(VLOOKUP($B2,'Emp Lists'!$B:$F,5,FALSE)),($D2+1),FALSE)...
  10. S

    HLOOKUP multiple table arrays HOW DO I DO THIS? Pls help

    Hi Guys, I have a HLOOKUP formula that looks at a table on a separate tab. I have 2 further tables in the same format but both on different tabs which I also want the formula to read. Basically the lookup value element of the formula contains all of the possibilities within those 3 seperate...
  11. M

    alternative to vlookup for cell 3, 4, 5 etc

    This is my formula that looks at the date then goes to the tab named events check dates and if date matches it posts the information back at the first page. =VLOOKUP(B3,event,8,0) The problem is if I have items in columns 2,3, 4, 5 etc vlookup ignores these columns. I need the formula to do as...
  12. V

    hlookup & sumifs

    Hello, My file has company codes(CoCd) in col's and Profit centers(PC) in rows, with $'s assigned to them. I need to retrieve $'s specific to a PC and CoCd combination. Issue is sometimes same CoCds are in multiple columns. So can't do a simple sumif or Hlookup. E.g. B30 PC0001 B31 PC0002 B32...
  13. T

    Finding Date of Max Value

    Hi, I have a project I am working on where I am trying to find a max value in a row and then insert that max value into its own column and then next to that column create a new one that returns the date that the value occurred on. Any suggestions or functions for how I could find that date? I...
  14. 1

    Index/Match calc to address 3 criteria in multiple rows and columns using 1 ref table

    <tbody> SKU# 2301 2301 2301 50010 5000 50010 $ Units Stores $ Units Stores 1 23 3 50 15 2 48 2 6.99 1 50 17 2 48 3 30 6 50 6 1 48 4 0 0 50 15 2 48 5 100 20 50 15 2 48 6 72.32 15 50 7 1 48 7 6.99 1 50 18 2 48 8...
  15. H

    Array to do Hlookup

    How would I code an array to do a kind of hlookup? I have data on sheet1 in columns A:I. I have data on sheet2 in column A. I want to look at the value on sheet2 row A and see if it is listed in the data on sheet1 columns A:I. If it is, i want the value from row 3 of the column it's in on...
  16. A

    Lookup entire sheet return value in column below

    I need help to figure out how to best do this. Tried to search it and didn't see anything that works exactly. Here is what I want to do. I want to vlookup/or hlookup into a different sheet (Bottom table is what that sheet should look like) where the value is in different column and rows and...
  17. P

    Returning value above searched row

    I'm tryin to return a value above the searched row. I've tried a couple options and here is what I have tried: =HLOOKUP(LEFT(Y4, 4), CHOOSE({1;2},B3:M3,B1:M1), 2, FALSE) =INDEX(A1:M3,MATCH(LEFT(Y5,4),A1:M3,0),MATCH(LEFT(Y5,4),A1:M3,0)) =VLOOKUP("AISLE", A1:M3, MATCH(LEFT(Y6, 4), A1:M3, 0)...
  18. A

    Hyperlink not working

    Hi Id be grateful for some help with a hlookup formula which is supposed to return the value, but also any hyperlink associated with that value The formula is as follows =HYPERLINK(hlookup(C$1,Master!$C$1:$BM$150,ROW(),FALSE)),"") but the error is 'Cannot open the specified file' I've...
  19. R

    Using a column number in a Hlookup formula

    Hi I have a problem trying to use a column number in the formula below. HLOOKUP(N1,A1:A&INDIRECT(Q1),2) There is a column number that will appear in cell Q1 and I want the lookup range to work based on that, for example if there was an 8 in cell Q1, then Excel would read the Hlookup as...
  20. C

    I can't remember how my formula works

    I'm trying to add to spreadsheet I made a couple of years ago. I need to tweak a formula to put something similar-ish in another cell. I know what the formula ends up producing but can't remember how it works. Can anyone translate each of the steps in this for me please...

Some videos you may like

This Week's Hot Topics

Top