# Lookup Question

#### wolverine12

##### Board Regular
Does anyone know a way (either using Vlookup and Index/Match) of flagging if I hit more than once with the lookup.

Example:

I have account numnbers which have multiple customers...but the customer data is incomplete.

ACC# CUST#
1 A
1 B
1 (blank)
2 C
etc.

So I need to do the lookup by ACC# (as the blanks within the CUST# data are numerous) but I would like to know when I get multiple hits.

Any help would be greatly appreciated.

PS: Please don't ask why the data is like this...its a nightmare !!!!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### DonkeyOte

##### MrExcel MVP
can you not simply do a countif by account number?

so COUNTIF(A2:A500,1)

where A2:A500 contains the account data (& multiples)

1 could be a reference so if you had the account number in C1

COUNTIF(A2:A500,C1)

You can then amend this to tell you where it's greater than 0

=IF(COUNTIF(\$A\$2:\$A\$500,\$C1)>1,"Multiple Entries","OK")

#### wolverine12

##### Board Regular
I guess I wasn't clear on my end goal. I think I fixed my problem nesting two lookups (if, then).

thanks for the quick response...I'm sure I'll have more questions that I'll need some help on !

#### DonkeyOte

##### MrExcel MVP
Sorry Wolverine (please tell me you don't wear yellow spandex?) - think I get you now - were you wanting the locations of the multiple addresses on the list (hence the index?)

Replies
3
Views
325
Replies
5
Views
548
Replies
1
Views
710
Replies
0
Views
581
Replies
5
Views
398

1,181,919
Messages
5,932,793
Members
436,861
Latest member
eh19

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