What Function to use?
 

A B C D
1 Kevin P 320 ME #1 10.0
2 Kevin 350 NVT 10.0
3 Dennis M 325 SNH/NNH Day #1 10.0
4 Steve P 300 BOS/LCL 9.0
5 Mike C 260 WOR [Trac] 9.0
6 Tom 300 ALB 8.0
7 Mike D 200 WCT [Trac] 8.0
8 Laszlo 280 RHN [Trac] 8.0
9 Dennis M (Unload) BAEC '09 Show 2.0
10 Greg S (Load) 20th Annual Hudson HS 1.0

What function would I use for the following statement: If specific text is
found in the range A2:A10 then follow the row of that cell and report the
amount shown in column D

What Function to use?
 

One simple way is via vlookup, set for exact match

Source data as posted assumed in Sheet1, cols A to D
In another sheet,
Assume the names to be looked up are listed in A2 down
In B2: =IF(A2="","",VLOOKUP(A2,Sheet1!A:D,4,0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"Dick" wrote:
A B C D
1 Kevin P 320 ME #1 10.0
2 Kevin 350 NVT 10.0
3 Dennis M 325 SNH/NNH Day #1 10.0
4 Steve P 300 BOS/LCL 9.0
5 Mike C 260 WOR [Trac] 9.0
6 Tom 300 ALB 8.0
7 Mike D 200 WCT [Trac] 8.0
8 Laszlo 280 RHN [Trac] 8.0
9 Dennis M (Unload) BAEC '09 Show 2.0
10 Greg S (Load) 20th Annual Hudson HS 1.0

What function would I use for the following statement: If specific text is
found in the range A2:A10 then follow the row of that cell and report the
amount shown in column D

What Function to use?
 

Hi,

You can use the VLOOKUP() function. Let's say you have Kevin (the specific
text) typed in cell A12. You can use the following formula

=vlookup(A12,A1:D10,4,0)

Since the data does not appear clearly in the question, I am assuming that
the name is in column A and other detail are in columns B:C.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dick" wrote in message
...
A B C
D
1 Kevin P 320 ME #1 10.0
2 Kevin 350 NVT 10.0
3 Dennis M 325 SNH/NNH Day #1 10.0
4 Steve P 300 BOS/LCL 9.0
5 Mike C 260 WOR [Trac] 9.0
6 Tom 300 ALB 8.0
7 Mike D 200 WCT [Trac] 8.0
8 Laszlo 280 RHN [Trac] 8.0
9 Dennis M (Unload) BAEC '09 Show 2.0
10 Greg S (Load) 20th Annual Hudson HS 1.0

What function would I use for the following statement: If specific text is
found in the range A2:A10 then follow the row of that cell and report the
amount shown in column D

What Function to use?
 

Thanks for the reply, and that would work except Kevin will not always be in
cell A12. The text "Kevin" in column A will always be constant but will be
located somewhere in the range A2:A14. I want to find the Text "Kevin" in
the range A2:A14 and return the value in column D in the same row "Kevin" is
found.

"Ashish Mathur" wrote:

Hi,

You can use the VLOOKUP() function. Let's say you have Kevin (the specific
text) typed in cell A12. You can use the following formula

=vlookup(A12,A1:D10,4,0)

Since the data does not appear clearly in the question, I am assuming that
the name is in column A and other detail are in columns B:C.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dick" wrote in message
...
A B C
D
1 Kevin P 320 ME #1 10.0
2 Kevin 350 NVT 10.0
3 Dennis M 325 SNH/NNH Day #1 10.0
4 Steve P 300 BOS/LCL 9.0
5 Mike C 260 WOR [Trac] 9.0
6 Tom 300 ALB 8.0
7 Mike D 200 WCT [Trac] 8.0
8 Laszlo 280 RHN [Trac] 8.0
9 Dennis M (Unload) BAEC '09 Show 2.0
10 Greg S (Load) 20th Annual Hudson HS 1.0

What function would I use for the following statement: If specific text is
found in the range A2:A10 then follow the row of that cell and report the
amount shown in column D

What Function to use?
 

Hi,

Please input other names before A12 and copy the formula in cell B12 down.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dick" wrote in message
...
Thanks for the reply, and that would work except Kevin will not always be
in
cell A12. The text "Kevin" in column A will always be constant but will
be
located somewhere in the range A2:A14. I want to find the Text "Kevin" in
the range A2:A14 and return the value in column D in the same row "Kevin"
is
found.

"Ashish Mathur" wrote:

Hi,

You can use the VLOOKUP() function. Let's say you have Kevin (the
specific
text) typed in cell A12. You can use the following formula

=vlookup(A12,A1:D10,4,0)

Since the data does not appear clearly in the question, I am assuming
that
the name is in column A and other detail are in columns B:C.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dick" wrote in message
...
A B C
D
1 Kevin P 320 ME #1 10.0
2 Kevin 350 NVT 10.0
3 Dennis M 325 SNH/NNH Day #1 10.0
4 Steve P 300 BOS/LCL 9.0
5 Mike C 260 WOR [Trac] 9.0
6 Tom 300 ALB 8.0
7 Mike D 200 WCT [Trac] 8.0
8 Laszlo 280 RHN [Trac] 8.0
9 Dennis M (Unload) BAEC '09 Show 2.0
10 Greg S (Load) 20th Annual Hudson HS 1.0

What function would I use for the following statement: If specific text
is
found in the range A2:A10 then follow the row of that cell and report
the
amount shown in column D

What Function to use?
 

On Sun, 22 Feb 2009 16:15:00 -0800, Dick
wrote:

A B C D
1 Kevin P 320 ME #1 10.0
2 Kevin 350 NVT 10.0
3 Dennis M 325 SNH/NNH Day #1 10.0
4 Steve P 300 BOS/LCL 9.0
5 Mike C 260 WOR [Trac] 9.0
6 Tom 300 ALB 8.0
7 Mike D 200 WCT [Trac] 8.0
8 Laszlo 280 RHN [Trac] 8.0
9 Dennis M (Unload) BAEC '09 Show 2.0
10 Greg S (Load) 20th Annual Hudson HS 1.0

What function would I use for the following statement: If specific text is
found in the range A2:A10 then follow the row of that cell and report the
amount shown in column D

Take a look at VLOOKUP
--ron

What Function to use?
 

Wonder whether you're hinting at a fuzzy kind of match for the name?

You could use something like this
In say, F2, normal ENTER to confirm:
=INDEX(D$2:D$14,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(" Kevin",A$2:A$14)),),0))

The above will return col D's value for the 1st fuzzy match for the name
found in A2:A14. If you need to return all possible matches, suggest that you
just apply autofilter on col A, and use Custom contains (the penultimate
selection in the "Show rows where" dropdown)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"Dick" wrote:
Thanks for the reply, and that would work except Kevin will not always be in
cell A12. The text "Kevin" in column A will always be constant but will be
located somewhere in the range A2:A14. I want to find the Text "Kevin" in
the range A2:A14 and return the value in column D in the same row "Kevin" is
found.

What Function to use?
 

Hi,

All the answers you are getting are correct.

Maybe we should say enter the name you want to look up in cell H1, for
example Kevin and then the formula

=VLOOKUP(H1,A2:D10,4,)

Maybe what is confusing the issue is the fact that somebody choose A12 to
enter Kevin in rather than in a cell in another part of the spreadsheet?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Dick" wrote:

Thanks for the reply, and that would work except Kevin will not always be in
cell A12. The text "Kevin" in column A will always be constant but will be
located somewhere in the range A2:A14. I want to find the Text "Kevin" in
the range A2:A14 and return the value in column D in the same row "Kevin" is
found.

"Ashish Mathur" wrote:

Hi,

You can use the VLOOKUP() function. Let's say you have Kevin (the specific
text) typed in cell A12. You can use the following formula

=vlookup(A12,A1:D10,4,0)

Since the data does not appear clearly in the question, I am assuming that
the name is in column A and other detail are in columns B:C.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dick" wrote in message
...
A B C
D
1 Kevin P 320 ME #1 10.0
2 Kevin 350 NVT 10.0
3 Dennis M 325 SNH/NNH Day #1 10.0
4 Steve P 300 BOS/LCL 9.0
5 Mike C 260 WOR [Trac] 9.0
6 Tom 300 ALB 8.0
7 Mike D 200 WCT [Trac] 8.0
8 Laszlo 280 RHN [Trac] 8.0
9 Dennis M (Unload) BAEC '09 Show 2.0
10 Greg S (Load) 20th Annual Hudson HS 1.0

What function would I use for the following statement: If specific text is
found in the range A2:A10 then follow the row of that cell and report the
amount shown in column D