CODEWITHSUNDEEP
excelexcel-formulavba
thisbenroberts
thisbenroberts

Reputation: 21

Looking for a single value in an Excel column with multiple matches

I have a list of client numbers and I need to search through a list of account numbers to find out if the client has a particular account number in their file.

My original sheet looks like this

original

My list of account numbers looks like this

Expected results look like this

results

Originally, I concatenated the client and account numbers then did a VLOOKUP but I'm sure there's a better way. Maybe using VBA instead of a formula in the worksheet. Can anyone help me out?

Upvotes: 0

Views: 532

Answers (5)

BruceWayne
BruceWayne

Reputation: 23285

Here's another way, using Index/Match. Break your 217 1008 column into two, using Text to Columns, Space Delimiter. Then say these are in range J1:J10 (217, 218, etc.), and in K1:K10 is 1008, 1009, etc.:

=IF(NOT(ISERROR(INDEX($J$1:$J$10,MATCH($A2&B$1,$J$1:$J$10&$K$1:$K$10,0)))),"X","")
(enter as array with CTRL+SHIFT+ENTER)

Where A2 is the start of your 217 numbers going down, and B1 is the 1008 start going right.

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19299

If you Client and Accounts is in the same column, you can use this which tries to MATCH the concatenation of the row and column header (with space):

=IF(NOT(ISERROR(MATCH($C4&" "&D$1,$A$2:$A$11,0))),"X","")

E.g.:

enter image description here

Upvotes: 1

JetSetJim
JetSetJim

Reputation: 78

As @pnuts says, a PivotTable will get you your results table, but it will soon get unwieldy if you have large numbers of clients/accounts. Your VLOOKUP of a CONCATENATEd value is a good way to go unless you don't want to modify the data.

If you're only doing it as a single shot check on a small number of clients/accounts, perhaps just using the auto-filters on the data might be a simpler solution?

Upvotes: 1

JNevill
JNevill

Reputation: 50308

If your Client and Accounts list is in seperate cells, you could use Countifs():

enter image description here

Upvotes: 1

Kyle
Kyle

Reputation: 2545

I will assume the the table shown in results starts in cell A1, so your formula will be typed in B3 and copied right and down. I will also assume you account list is in a named range Acct. 

=IF(MATCH($A3&" "&B$14,Acct)>0,"X","")

Copy this to all cells in B3:E5 in your example.

Upvotes: 0

Related Questions