Looking up a value to the left in a table

[cpg84]

Hi,

I want to find a name in a table and return what the value of the cell next to it is. Here's a basic layout of the table:

Champion Townsville
Runner Up Singapore
Lost SF Cairns
Lost SF Melbourne
Lost QF Brisbane
Lost QF Sydney
Lost QF Adelaide
Lost QF New Zealand
9th Wollongong
10th Perth
11th South
12th Gold Coast
13th West Sydney

For each team I want to see where they finished. E.g. For Townsville, it will say Champion and so on down the list. The cell that I want their "finishing position" to appear in, is on a different worksheet to the table.

Any help would be appreciated. Im totally confused between MATCH and LOOKUP, maybe there is a better formula.

Thanks

 

[pgc01]

Hi Cammy84

Use:
=INDEX(A1:A13,MATCH(E2,B1:B13,0))

Hope this helps
PGC

Book1
	A	B	C	D	E	F	G
1	Champion	Townsville			Place	Finishingposition
2	RunnerUp	Singapore			Perth	10th
3	LostSF	Cairns
4	LostSF	Melbourne
5	LostQF	Brisbane
6	LostQF	Sydney
7	LostQF	Adelaide
8	LostQF	NewZealand
9	9th	Wollongong
10	10th	Perth
11	11th	South
12	12th	GoldCoast
13	13th	WestSydney
14
15
Sheet2

 

[Golden Age]

I know what you mean when you express confusion about the LOOKUP and MATCH functions. But, as far as I understand, short of using insanely advanced code and tricks that only true EXCEL gurus have mastered (which I am NOT), the combination of LOOKUP and MATCH are the only way to look things up to the left.

I haven't experimented with this scenario myself yet but you might want to check out the following website and see what you can find to help solve your challenge.

http://www.ozgrid.com/Excel/left-lookup.htm

Hope you can solve it!!

 

[cpg84]

I tried that site and it doesn't help.

The problem I can see is that you can't index or match with data on another worksheet. Apart from that, it would work.

Any suggestions???

Thanks for the advice anyway guys

 

[tusharm]

I tried that site and it doesn't help.

The problem I can see is that you can't index or match with data on another worksheet. Apart from that, it would work.

Any suggestions???

Thanks for the advice anyway guys

There are absolutely no problems using INDEX and MATCH across worksheets. What did you try and how did it not work?

 

[Golden Age]

I just did a little experimenting and found that if you add the worksheet number or worksheet name in front of the INDEX/MATCH formula you can have it appear on other worksheets but still within the same workbook.
I apologize for the crudity of my illustration, but the example I cited you on ozgrid.com, showed you how to set up the the Left Lookup on the same sheet (sheet1, and it appeared as something like what is just below).

The "A B C D" represent the columns and the "1 2 3 4 5 6" represent the rows. There is a "Name" column, an "Age" column, an ID column, and a "Country" column. The "names" are set up first name and last initial, the "countries" are by two letter abbreviation.

A B C D
1 Name Age ID# Country
2 Dave H 23 R215 AU
3 Bill T 57 R245 CA
4 Kate C 34 K345 USA
5 Jenn W 37 K346 CA
6 Cameron F 26 L592 JA
7
8 =index($a$1:$a$6,match("R215",$c$1:$c$6,0),1)


BUT, if you go to say sheet number 2 (or whatever, just stay within the same workbook) you need to modify your formula slightly to tell EXCEL to look at another worksheet. Keeping all of your data on sheet 1, type the following formula into whatever cell you need on a different sheet;

=index(sheet1!$a$1:$a$6,match("R215",sheet1!$c$1:$c$6,0),1)

Note: if your sheets are actually Named, use those names but remember to include the exclamation mark.

 

[cpg84]

I used =INDEX(Results!Q181:Q193,MATCH("Adelaide",Results!R181:S193,0),1)

Obviously, Results is the name of the other worksheet I am getting the data from. Column R and S are merged together though, this column has the team names in it.

It comes up with #NA. Is there an add in I need? I didnt think there was.

 

[Aladin Akyurek]

I used =INDEX(Results!Q181:Q193,MATCH("Adelaide",Results!R181:S193,0),1)

Obviously, Results is the name of the other worksheet I am getting the data from. Column R and S are merged together though, this column has the team names in it.

It comes up with #NA. Is there an add in I need? I didnt think there was.

Better not to merge cells the formulas must refer to, so unmerge R:S. And invoke:

=INDEX(Results!$Q$181:$Q$193,MATCH("Adelaide",Results!$R$181:$R$193,0))

for I expect the match range is in R. Note that MATCH needs a vector and can't work with a matrix.

 

[cpg84]

Got it. I have kept them merged but changed S193 to R193 and it works. I was starting to think that was the reason it wasnt working.

Thank you everyone.