Searching an Alphabetized list for data, then RESORT

F

FLKULCHAR

I have a list of NFL teams in column A (alphabetized) with their rating in
column B:


1 COL B COL C
2
3 Balt 10
4 Chicago 8
5 Cleve 13
6 Dallas 17
7 Denver 9
8 Green Bay 15

etc., etc.

I wish to search for the entire 32 teams (columns 3 thru 34, column B) for a
team name, then insert its rating (col. C) into a new list.

For example, on another spreadsheet I have the weekly schedule.

In row 4 I have:

col A col B col C col D col E
4 Balt. Green Bay ??? ???

I wish to insert the teams' rating into column D (for Balt) and column E
(for Green Bay). [in other words, a 10 and a 15]

How is this done???

I believe it is some type of array look-up routine...search for the match,
then enter the data.

Please advise.

FL KULCHAR

please also notify (e-mail address removed)

THANK YOU
 
B

bpeltzer

Use Excel's vlookup function.
In cell D4 (where you want the 10 returned): =vlookup(b4,Sheet1!B:C,2,false)
and in E4: =vlookup(c4,Sheet1!B:C,2,false)
(You may need to change the Sheet1 reference if the name of your first
worksheet is different and/or it's in a different workbook). As you're
entering the formula, the easiest way to create the cell references will be
just to click to select columns B and C from your first worksheet.
One caveat: you'll want to use the exact same team names (ex Balt vs Balt.)
in both worksheets.
 
F

FLKULCHAR

I keep getting a #REF error, and I do not understand why?

pls help...we are almost there

bpeltzer said:
Use Excel's vlookup function.
In cell D4 (where you want the 10 returned): =vlookup(b4,Sheet1!B:C,2,false)
and in E4: =vlookup(c4,Sheet1!B:C,2,false)
(You may need to change the Sheet1 reference if the name of your first
worksheet is different and/or it's in a different workbook). As you're
entering the formula, the easiest way to create the cell references will be
just to click to select columns B and C from your first worksheet.
One caveat: you'll want to use the exact same team names (ex Balt vs Balt.)
in both worksheets.


FLKULCHAR said:
I have a list of NFL teams in column A (alphabetized) with their rating in
column B:


1 COL B COL C
2
3 Balt 10
4 Chicago 8
5 Cleve 13
6 Dallas 17
7 Denver 9
8 Green Bay 15

etc., etc.

I wish to search for the entire 32 teams (columns 3 thru 34, column B) for a
team name, then insert its rating (col. C) into a new list.

For example, on another spreadsheet I have the weekly schedule.

In row 4 I have:

col A col B col C col D col E
4 Balt. Green Bay ??? ???

I wish to insert the teams' rating into column D (for Balt) and column E
(for Green Bay). [in other words, a 10 and a 15]

How is this done???

I believe it is some type of array look-up routine...search for the match,
then enter the data.

Please advise.

FL KULCHAR

please also notify (e-mail address removed)

THANK YOU
 
F

FLKULCHAR

Never Mind...your rsvp is PERFECT,,,

thank you very much...you have helped me immeasurably!

FLK

bpeltzer said:
Use Excel's vlookup function.
In cell D4 (where you want the 10 returned): =vlookup(b4,Sheet1!B:C,2,false)
and in E4: =vlookup(c4,Sheet1!B:C,2,false)
(You may need to change the Sheet1 reference if the name of your first
worksheet is different and/or it's in a different workbook). As you're
entering the formula, the easiest way to create the cell references will be
just to click to select columns B and C from your first worksheet.
One caveat: you'll want to use the exact same team names (ex Balt vs Balt.)
in both worksheets.


FLKULCHAR said:
I have a list of NFL teams in column A (alphabetized) with their rating in
column B:


1 COL B COL C
2
3 Balt 10
4 Chicago 8
5 Cleve 13
6 Dallas 17
7 Denver 9
8 Green Bay 15

etc., etc.

I wish to search for the entire 32 teams (columns 3 thru 34, column B) for a
team name, then insert its rating (col. C) into a new list.

For example, on another spreadsheet I have the weekly schedule.

In row 4 I have:

col A col B col C col D col E
4 Balt. Green Bay ??? ???

I wish to insert the teams' rating into column D (for Balt) and column E
(for Green Bay). [in other words, a 10 and a 15]

How is this done???

I believe it is some type of array look-up routine...search for the match,
then enter the data.

Please advise.

FL KULCHAR

please also notify (e-mail address removed)

THANK YOU
 
B

bpeltzer

Can you paste the formula that's generating the #REF? Also tell us what cell
the formula is in, and where your source data (the table with each team's
rating) is located -- workbook name, worksheet name and cell range.
Usually, the #REF comes about when the 'column index' in the vlookup is
greater than the number of columns in your 'table array'. Ex: if your table
array is B:C (two columns) and your column index is 3, you'd get the #REF.

FLKULCHAR said:
I keep getting a #REF error, and I do not understand why?

pls help...we are almost there

bpeltzer said:
Use Excel's vlookup function.
In cell D4 (where you want the 10 returned): =vlookup(b4,Sheet1!B:C,2,false)
and in E4: =vlookup(c4,Sheet1!B:C,2,false)
(You may need to change the Sheet1 reference if the name of your first
worksheet is different and/or it's in a different workbook). As you're
entering the formula, the easiest way to create the cell references will be
just to click to select columns B and C from your first worksheet.
One caveat: you'll want to use the exact same team names (ex Balt vs Balt.)
in both worksheets.


FLKULCHAR said:
I have a list of NFL teams in column A (alphabetized) with their rating in
column B:


1 COL B COL C
2
3 Balt 10
4 Chicago 8
5 Cleve 13
6 Dallas 17
7 Denver 9
8 Green Bay 15

etc., etc.

I wish to search for the entire 32 teams (columns 3 thru 34, column B) for a
team name, then insert its rating (col. C) into a new list.

For example, on another spreadsheet I have the weekly schedule.

In row 4 I have:

col A col B col C col D col E
4 Balt. Green Bay ??? ???

I wish to insert the teams' rating into column D (for Balt) and column E
(for Green Bay). [in other words, a 10 and a 15]

How is this done???

I believe it is some type of array look-up routine...search for the match,
then enter the data.

Please advise.

FL KULCHAR

please also notify (e-mail address removed)

THANK YOU
 

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

Top