How can I use a lookup command to find only an exact match

[Sniper22]

I run a fantasy hockey league and need to be able to use lookup to find only an exact match to a name. Currently I have to use a switch of some sort so that it doesn't return stats of a name thats close if the name I want to match isn't there.

Something like this to return Goals scored for a particular player:

=IF(($C5="A"),LOOKUP($A5,'Current Stats'!$A$1:$A$1000,'Current Stats'!$$D$1:$D$1000)," ")

Typing an "A" into the cells in column C is the manual switch I use, once I know a player is on the list, but there must be a way to only return an exact match and not something that's close to the lookup criteria.

Any help would be greatly appreciated.

 

[Joe4]

If you can use the VLOOKUP formula instead, the last argument is for exact match (check it out in Excel help for details and examples).

 

[Sniper22]

Thanks jm14,
I've tried this already. The problem is when the match isn't there, it returns an error, I want the cell to be just left blank when a match isn't found.

 

[hcabs99]

Use the ISNA function, this returns a blank rather than an N/A, example formula below

=IF(ISNA(VLOOKUP(G641,locations,3,FALSE))," ",(VLOOKUP(G641,locations,3,FALSE)))

Hope that helps

 

[Sniper22]

It leaves the cell blank for names that don't match, but returns REF# for those that do instead of the number I want. Unless I'm doing something wrong (probably).

I'll layout a simple sheet to help me understand how this works.

on Sheet1 is the data

Col A Col B
Row 1 Player A 0
Row 2 Player B 2
Row 3 Player C 1

On Sheet2 is a list of players on teams
Col A Col B
Row 1 Player A 0
Row 2 Player D
Row 3 Player B 1

The formula on Sheet2 in Column B is to look up the players name on Sheet1 and returns the value from column B if it matches, otherwise it leaves it blank.

What would the formula look like?
Thanks

 

[Sniper22]

OK I've got it.....Thanks alot for your help.

=IF(ISNA(VLOOKUP($A1,Sheet1!$A$1:$A$3,$A1,FALSE))," ",LOOKUP($A1,Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3))

I used Lookup for the 2nd part.....couldn't get Vlookup working for some reason.

 

[hcabs99]

no problem , any other issues , drop me a line via my website hcab-excel-solutions.co.uk

If you send me your spreadsheet i will try and work out why the vlookup does not work correctly

 

[Aladin Akyurek]

OK I've got it.....Thanks alot for your help.

=IF(ISNA(VLOOKUP($A1,Sheet1!$A$1:$A$3,$A1,FALSE))," ",LOOKUP($A1,Sheet1!$A$1:$A$3,Sheet1!$B$1:$B$3))

I used Lookup for the 2nd part.....couldn't get Vlookup working for some reason.

That sounds strange.

Is A1:B3 on Sheet1 sorted in ascending order on column A? And, if so, can you also maintain it sorted when new data is added?