Finding a string/using adjacent data question

It's a convuluted question that I hope has an easy answer. I'm rather
new to Excel formulas, so I've been trying to find stuff about this on
the internet but with not much luck. If anyone can help me at all I
would greatly appreciate it.

I have text strings in column A, A2-A335. I have numerical values that
correspond to each text string in columns B-O.

What I'm hoping to do is run a search for a given text string in column
A and then use the numerical values in D, H, and O for further
calculations.

I want to be able to type in a name in column Q (for example) and have
the formula return the values from the same row in columns D,H, and O
in descending format below the name. So, if I typed in, 'Bob,' in cell
Q2, for example, below my name it might return:

45 (from column D)
73 (from column H)
26 (from column O)

If you're still confused by what I'm requesting, I'll rephrase it as
best I can in sentence form:

First cell below the name:

"Search column A for the string given directly above this cell (Q2,
perhaps). If the specified string is found, return the number in that
row from the D column."

Second cell below the name:

"Search column A for the string given two cells above this cell (again,
Q2, perhaps). If the specified string is found, return the number in
that row from the H column."

Third cell below the name:

""Search column A for the string given three cells above this cell
(again, Q2, perhaps). If the specified string is found, return the
number in that row from the O column."



I'm not sure if this can be done but if anyone knows that it can be and
how, I would really appreciate it. If you need more information, say
the word and it will be here.

Hi
One way,
in Q3 =VLOOKUP($Q$2,$A$2:$O$17,4,0)
in Q4 =VLOOKUP($Q$2,$A$2:$O$17,8,0)
in Q5 =VLOOKUP($Q$2,$A$2:$O$17,15,0)

--
Regards

Roger Govier


<[email protected]> wrote in message
news:[email protected]...
> It's a convuluted question that I hope has an easy answer. I'm rather
> new to Excel formulas, so I've been trying to find stuff about this on
> the internet but with not much luck. If anyone can help me at all I
> would greatly appreciate it.
>
> I have text strings in column A, A2-A335. I have numerical values that
> correspond to each text string in columns B-O.
>
> What I'm hoping to do is run a search for a given text string in
> column
> A and then use the numerical values in D, H, and O for further
> calculations.
>
> I want to be able to type in a name in column Q (for example) and have
> the formula return the values from the same row in columns D,H, and O
> in descending format below the name. So, if I typed in, 'Bob,' in cell
> Q2, for example, below my name it might return:
>
> 45 (from column D)
> 73 (from column H)
> 26 (from column O)
>
> If you're still confused by what I'm requesting, I'll rephrase it as
> best I can in sentence form:
>
> First cell below the name:
>
> "Search column A for the string given directly above this cell (Q2,
> perhaps). If the specified string is found, return the number in that
> row from the D column."
>
> Second cell below the name:
>
> "Search column A for the string given two cells above this cell
> (again,
> Q2, perhaps). If the specified string is found, return the number in
> that row from the H column."
>
> Third cell below the name:
>
> ""Search column A for the string given three cells above this cell
> (again, Q2, perhaps). If the specified string is found, return the
> number in that row from the O column."
>
>
>
> I'm not sure if this can be done but if anyone knows that it can be
> and
> how, I would really appreciate it. If you need more information, say
> the word and it will be here.
>

in Q3: =VLOOKUP($Q$2,A2:D335,4,FALSE)
in Q4: =VLOOKUP($Q$2,A2:H335,8,FALSE)
in Q5: =VLOOKUP($Q$2,A2:O335,15,FALSE)


Regards,
Stefi

„[email protected]” ezt �*rta:

> It's a convuluted question that I hope has an easy answer. I'm rather
> new to Excel formulas, so I've been trying to find stuff about this on
> the internet but with not much luck. If anyone can help me at all I
> would greatly appreciate it.
>
> I have text strings in column A, A2-A335. I have numerical values that
> correspond to each text string in columns B-O.
>
> What I'm hoping to do is run a search for a given text string in column
> A and then use the numerical values in D, H, and O for further
> calculations.
>
> I want to be able to type in a name in column Q (for example) and have
> the formula return the values from the same row in columns D,H, and O
> in descending format below the name. So, if I typed in, 'Bob,' in cell
> Q2, for example, below my name it might return:
>
> 45 (from column D)
> 73 (from column H)
> 26 (from column O)
>
> If you're still confused by what I'm requesting, I'll rephrase it as
> best I can in sentence form:
>
> First cell below the name:
>
> "Search column A for the string given directly above this cell (Q2,
> perhaps). If the specified string is found, return the number in that
> row from the D column."
>
> Second cell below the name:
>
> "Search column A for the string given two cells above this cell (again,
> Q2, perhaps). If the specified string is found, return the number in
> that row from the H column."
>
> Third cell below the name:
>
> ""Search column A for the string given three cells above this cell
> (again, Q2, perhaps). If the specified string is found, return the
> number in that row from the O column."
>
>
>
> I'm not sure if this can be done but if anyone knows that it can be and
> how, I would really appreciate it. If you need more information, say
> the word and it will be here.
>
>

you want the VLOOKUP function. It will look like this.

Q1: "Text Here"
Q2: =VLOOKUP(Q1,A2:O335,4,FALSE)
Q2: =VLOOKUP(Q1,A2:O335,8,FALSE)
Q3: =VLOOKUP(Q1,A2:O335,15,FALSE)

"[email protected]" wrote:

> It's a convuluted question that I hope has an easy answer. I'm rather
> new to Excel formulas, so I've been trying to find stuff about this on
> the internet but with not much luck. If anyone can help me at all I
> would greatly appreciate it.
>
> I have text strings in column A, A2-A335. I have numerical values that
> correspond to each text string in columns B-O.
>
> What I'm hoping to do is run a search for a given text string in column
> A and then use the numerical values in D, H, and O for further
> calculations.
>
> I want to be able to type in a name in column Q (for example) and have
> the formula return the values from the same row in columns D,H, and O
> in descending format below the name. So, if I typed in, 'Bob,' in cell
> Q2, for example, below my name it might return:
>
> 45 (from column D)
> 73 (from column H)
> 26 (from column O)
>
> If you're still confused by what I'm requesting, I'll rephrase it as
> best I can in sentence form:
>
> First cell below the name:
>
> "Search column A for the string given directly above this cell (Q2,
> perhaps). If the specified string is found, return the number in that
> row from the D column."
>
> Second cell below the name:
>
> "Search column A for the string given two cells above this cell (again,
> Q2, perhaps). If the specified string is found, return the number in
> that row from the H column."
>
> Third cell below the name:
>
> ""Search column A for the string given three cells above this cell
> (again, Q2, perhaps). If the specified string is found, return the
> number in that row from the O column."
>
>
>
> I'm not sure if this can be done but if anyone knows that it can be and
> how, I would really appreciate it. If you need more information, say
> the word and it will be here.
>
>

Hi

I'm sorry, I was testing on a smaller range and only went to row 17. You
need to extend to row 335
The formulae should also be wrapped in IF() statements also, to prevent
#N/A when there is no value entered in Q2

in Q3 =IF($Q$2="","",VLOOKUP($Q$2,$A$2:$O$335,4,0))
in Q4 =IF($Q$2="","",VLOOKUP($Q$2,$A$2:$O$335,8,0))
in Q5 =IF($Q$2="","",VLOOKUP($Q$2,$A$2:$O$335,21,0))


--
Regards

Roger Govier


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi
> One way,
> in Q3 =VLOOKUP($Q$2,$A$2:$O$17,4,0)
> in Q4 =VLOOKUP($Q$2,$A$2:$O$17,8,0)
> in Q5 =VLOOKUP($Q$2,$A$2:$O$17,15,0)
>
> --
> Regards
>
> Roger Govier
>
>
> <[email protected]> wrote in message
> news:[email protected]...
>> It's a convuluted question that I hope has an easy answer. I'm rather
>> new to Excel formulas, so I've been trying to find stuff about this
>> on
>> the internet but with not much luck. If anyone can help me at all I
>> would greatly appreciate it.
>>
>> I have text strings in column A, A2-A335. I have numerical values
>> that
>> correspond to each text string in columns B-O.
>>
>> What I'm hoping to do is run a search for a given text string in
>> column
>> A and then use the numerical values in D, H, and O for further
>> calculations.
>>
>> I want to be able to type in a name in column Q (for example) and
>> have
>> the formula return the values from the same row in columns D,H, and O
>> in descending format below the name. So, if I typed in, 'Bob,' in
>> cell
>> Q2, for example, below my name it might return:
>>
>> 45 (from column D)
>> 73 (from column H)
>> 26 (from column O)
>>
>> If you're still confused by what I'm requesting, I'll rephrase it as
>> best I can in sentence form:
>>
>> First cell below the name:
>>
>> "Search column A for the string given directly above this cell (Q2,
>> perhaps). If the specified string is found, return the number in that
>> row from the D column."
>>
>> Second cell below the name:
>>
>> "Search column A for the string given two cells above this cell
>> (again,
>> Q2, perhaps). If the specified string is found, return the number in
>> that row from the H column."
>>
>> Third cell below the name:
>>
>> ""Search column A for the string given three cells above this cell
>> (again, Q2, perhaps). If the specified string is found, return the
>> number in that row from the O column."
>>
>>
>>
>> I'm not sure if this can be done but if anyone knows that it can be
>> and
>> how, I would really appreciate it. If you need more information, say
>> the word and it will be here.
>>
>
>