Cell lookups by adjacent column's text entry

[jea876]

Here's a problem that is seriously stumping me, I've done my best to explain it thoroughly.

I want to reproduce (in another tab) the numerical value contained in a cell on another worksheet. The thing is, the cell I need to locate can only be identified by its row number and the fact that it is next to a cell with text (that I know) in it. For example: I know that a number (that I am trying to reproduce), say 12345, is in row 8, directly to the right of the cell that says "Identify," but I don't know the column that "Identify" is in.

On a more big-picture scope, I have a TON of columns, some of which contain text, and the next two columns (for each column containing text) display numbers (they are coded dates). The text is the name of a project phase, and the numbers are the start and end dates. I know each row that the numbers I want are in, but because the name of the phase may be in any of the columns, I can't simply refer to a specific column; I have to use some sort of lookup function.

To clarify my problem. I am looking for the number 12345 next to the cell containing "Identify" in row 8. But, "Identify" can be in column A, B, C, ...., or AZ, and in row 9, "Identify" may show up in a different column. How do I create a function to spit out 12345? It must also be able to identify the number next to "Identify" in row 9 when I change the formula to reference row 9.

If you can figure this out, you should probably quit your job and start an excel-based consulting firm.

[martindwilson]

grasshopper , masters of the forum require a sample workbook, the true path to excel enlightenment may be thus revealed to you

[MarvinP]

Hi jea876 and welcome to the forum,

See he attached for your answer.

Please Login or Register  to view this content.

Study upon the Match and Offset functions.

[Haseeb Avarakkan]

Hello,

Assume your data is in Sheet1!A:AZ. if so try,

Please Login or Register  to view this content.

This is an Array Formula, must hit CONTROL+SHIFT+ENTER, not just ENTER

[jea876]

Thank you, hopefully that is more appropriate.

[jea876]

I would, but because it is for my job, the data is confidential, no matter how impossible it would be for anyone to profit or gain insight off of a screen shot. Unfortunately, I am left to poorly describing a complex problem with words.

[shg]

Thank you. Earlier replies restored.

[bentleybob]

If you know the row number (8) and the text it is adjacent to (I've assumed it is to the right of "Identify" in Sheet 2), then with Identify in A2 and 8 in B2, you can use:

Please Login or Register  to view this content.

[martindwilson]

rather than =OFFSET($A8,0,MATCH("identity",$A8:$M8,0))
i'd use =INDEX($A8:$M8,MATCH("identity",$A8:$M8,0)+1)
but i still maintain a sample mocked up work book would be usefulle it can be gibberish just as long as identity and a number are somwhere in it and show us what is required from where

[jea876]

@martindwilson

Thank you! I've never had to use the Index function before, and now I'll never forget it.

[jea876]

@ Haseeb A
Thanks! That helped a lot. Index was the trick