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:
- All relevant header texts in
'Sheet3'!1:1
are absolutely unique within that row.
- 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].
- 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?
- 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".
And a partial shot of my "SheetA".
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.