What Function to use?

D

Dick

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
 
M

Max

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
 
A

Ashish Mathur

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
 
D

Dick

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 said:
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 said:
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
Click to expand...
Click to expand...
 
A

Ashish Mathur

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Rosenfeld

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
Click to expand...

Take a look at VLOOKUP
--ron
 
M

Max

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
 
S

Shane Devenshire

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

a.c.f. November 2003 stats 33

Top