Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel workbook with several sheets. The column 'SheetA'!W:W lists text that can be found within a column header on row 'Sheet3'!1:1. For example, SheetA'!W42 contains "B16". Cell Sheet3'!CB1 has the header text "B16: Sample 40", so it would be the target column.

Example: Sample of 'SheetA'!W42

enter image description here

In 'SheetA'!CD42 there is a formula that calculates the sample number that needs to be located in 'Sheet3'!A:A. In the sample below, CD42 displays the calculated value "30", which can be found in 'Sheet3'!A32.

Example: Sample data from Sheet3 displaying the headers and intersection of the column header (B16) and row containing the sample number of interest:

enter image description here

The goal is to retrieve the contents of the cell on Sheet3 where the searched for column and row intersect and display it in a cell on SheetA. In the sample provided, on 'Sheet3'!CB:CB intersects row 32 and on sheetA the value "0.1950581843" would be displayed.

Does anyone know how this can be accomplished?

share|improve this question
    
I've read this a bunch of times and I'm still confused what you're try to do? Yes you can have a simple cell reference between sheets for example: =Sheet1!A2 but that's so simple that I think what you are trying to ask is how to do table lookups on one sheet from a table located on another sheet? –  Tyson Jul 20 at 17:10
    
@Tyson I think I'm going to try rewriting this with Excel's proper cell reference syntax, and encapsulating all cell contents in quotes. Maybe that will help. I'm twenty kinds of confused too, though. –  Iszi Jul 20 at 17:21
    
Ok. Did what I could to fix it. Still confused as hell. Also, Excel is not a database! –  Iszi Jul 20 at 17:26
    
Yes, it's a table lookup that I am trying to accomplish but I wasn't sure if this required vlookup, hlookup or if there was and easier solution. I will try to untangle the question so it easier to understand my request. –  user3857791 Jul 20 at 17:26
    
@user3857791 No, I'd leave it as is. Now that I've seen it with the pictures embedded inline, and proper cell references spelled out, I think I've got it figured out. –  Iszi Jul 20 at 17:28

1 Answer 1

up vote 1 down vote accepted

Given your above data, I'm going to make a few assumptions. If all of these are true, then an HLOOKUP can easily solve your issue. Otherwise, a more complex chain of functions may be needed.

Assumptions:

  1. All relevant header texts in 'Sheet3'!1:1 are absolutely unique within that row.
  2. All relevant header texts in 'Sheet3'!1:1 are of the format "[X]: [Y]", where:
    • [X] is a value that can be found in 'SheetA'!W:W.
    • [Y] is the value in 'SheetA'!X:X which is on the same row as [X].
  3. All possible values in 'SheetA'!CD:CD are searchable in 'Sheet3'!A:A, such that they will be on the same row as the data intended to be retrieved.
    • I'm just putting this in because I noticed there seems to be some repetition of these values in Sheet3, and wanted to make sure there were no conditions under which you might be searching for a value in 'SheetA'!CD:CD that was meant to be matched against another column instead (and therefore may produce inaccurate results if matched in 'Sheet3'!A:A).
    • If said repetition is strictly for ease of reading, might I suggest using the "Freeze Panes" feature, instead?
  4. All relevant values in 'Sheet3!A:A' are perfectly sequential, starting with "1" in 'Sheet3'!A3, never repeating within that column, and always sorted ascending.

Given the above assumptions, I built my own sheets with sample data which roughly represents your scenario.

Here's a partial screenshot of my "Sheet3".

enter image description here

And a partial shot of my "SheetA".

enter image description here

List of analogues:

  • Sheet3
    • My A:A = Your A:A, BY:BY:, CA:CA:, ...
    • My B:B, C:C, D:D, ... = Your B:B, BZ:BZ, CB:CB, ...
  • SheetA
    • My A:A = Your W:W
    • My B:B = Your X:X
    • My C:C = Your CD:CD
    • My D:D = Whatever column you want to drop the found data in.

As you can see in the second screenshot, the formula for D2 is:

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

Step-by-step walkthrough of the formula:

HLOOKUP lets you look horizontally through a cell range for a value, then return the value of a cell in the same column based on a relative row position. It takes four arguments, three of which are mandatory: lookup_value, table_array, row_index_num, [range_lookup]. This is the formula that will be doing the leg-work of finding the data you want in Sheet3, and pulling it into a cell in SheetA.

  • lookup_value is the value you want HLOOKUP to find first. This value must be present in the first row of table_array, because that's the only row that will be searched. HLOOKUP also will only return the first match, so these values should also be unique. Here, we're using CONCATENATE to build our search string.
    • CONCATENATE lets you put together various strings and values into one string. It accepts a series of arguments, ordered according to the sequence in which they should be placed in the resulting string.
      • A2 is our first argument for CONCATENATE. The first part of our string will be the "Sample Name" in A2.
      • ": " is our second argument to CONCATENATE. This puts the colon and space into the string, to match the format of values in 'Sheet3'!1:1.
      • B2 is our last argument for CONCATENATE. It pulls the "Sample ID" from B2, to complete the syntax used for headers in 'Sheet3'!1:1.
  • table_array is a reference to a range of cells you want HLOOKUP to work with. Remember that the first row must be the one that will contain lookup_value. This range must also encompass all possible values for row_index_num.
    • Sheet3!A:Y is a reference to all cells in columns A through Y (the only ones populated in my Sheet3), of Sheet3. This ensures that any data added in new rows later on will also be in scope of the search. If data was going to be added in new columns instead of rows, I would want to use the reference Sheet3!1:32 (currently, 32 is the last row populated in my Sheet3). If data might be added by new columns and new rows, I would reference the entire sheet with Sheet3!1:1048576 or Sheet3!A:XFD. (Note: The "entire sheet" reference is valid for Excel 2013. Earlier versions may have smaller row/column limitations - adjust 1048576 or XFD as appropriate.)
  • row_index_num is a positive integer representing a row position relative to the topmost row in table_array. This tells HLOOKUP which cell you want returned from the matching column. Note that, because HLOOKUP only searches for lookup_value in the top row of table_array, and row_index_num cannot be negative, you cannot use HLOOKUP (at least, not by itself) to return information from cells that are above lookup_value.
    • C2+2 - Since all values in 'Sheet3'!A:A are perfectly sequential, with no skipped integers, and will always be ordered ascending, we can use those values (also represented in 'SheetA'!C:C) as indicators of the row numbers for the data we want to find. The +2 is there to account for the fact that the numbering starts with "1" on row 3 of Sheet3.
  • [range_lookup] is an optional argument for HLOOKUP. Options are TRUE or FALSE, to indicate whether you want to allow approximate matches to be valid or if only exact matches should be permitted. Excel defaults to TRUE (approximate matching) if this argument is omitted, which often can result in undesired behavior - especially if your sheet isn't sorted in certain ways. So, we specify FALSE here to make sure HLOOKUP will only pick up an exact match.

Adapting the above to your sheet's layout, I believe this is the formula you'll need for cell 'SheetA'!CE42 (assuming that's where you want the data to be dropped in):

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

Note that, if your data in Sheet3 goes further to the right than column CB, and/or data may be added to further columns, you'll want to adjust table_array accordingly.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.