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

#### Sniper22

##### New Member
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### 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

##### New Member
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
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
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
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
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

##### MrExcel MVP
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?

Replies
2
Views
493
Replies
1
Views
586
Replies
4
Views
370
Replies
1
Views
454
Replies
9
Views
438

1,172,017
Messages
5,878,755
Members
433,370
Latest member
mcleven

### 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.

### Which adblocker are you using?

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

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