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

Sniper22

New Member
Joined
Oct 6, 2006
Messages
4
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,302
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 6, 2006
Messages
4
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

Active Member
Joined
May 9, 2006
Messages
257
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

New Member
Joined
Oct 6, 2006
Messages
4

ADVERTISEMENT

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

New Member
Joined
Oct 6, 2006
Messages
4
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

Active Member
Joined
May 9, 2006
Messages
257
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Forum statistics

Threads
1,136,584
Messages
5,676,661
Members
419,638
Latest member
GlenMc52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top