Lookup Question

wolverine12

Board Regular
Joined
Jun 6, 2002
Messages
76
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 Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Jun 6, 2002
Messages
76
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
Joined
Sep 6, 2002
Messages
9,123
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?)
 

Forum statistics

Threads
1,144,510
Messages
5,724,774
Members
422,576
Latest member
kayth891

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