VLOOKUP with duplicate identifiers 1

[tsardich]

Does anyone know if you could use a "vlookup" if you have duplicate identifier (ie. account #'s, SSN, etc). the problem i am having is i am returing the same value from just one account to all of the duplicate numbers. this is not helpful since they all have different values...any suggestions?? i have tried nesting functions and i have come up with nothing! thanks!

 

[bluedragon2]

You can use the SUMPRODUCT formula to enter multiple criteria and retur a certan value. If you give a small example of what you are looking for, I can give you an example.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[tsardich]

account # value
12345 10
12345 123
12345 67

i need to return this data to another sheet, but when i lookup the account # (12345) it returns the value "10" for all accounts??!! i am going to try your suggestion! thanks

 

[bluedragon2]

This is a little tricky. If you want all the values for this account number then SUMPRODUCT will not work for you. There are a few threads in this forum with a variation of the VLOOKUP to look for multiple entries. VBA may be you best bet. You could also sort the data and copy the rows you need.

If you wanted the sum of the values for the account then SUMPRODUCT will work for you.

I have a FAQ in this forum that explains SUMPRODUCT and looking up values.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[tsardich]

UURGGH i hate visual basic..it drives me nuts! i was going to use that as a last resort! thanks, i appreciate it!!

 

[Zathras]

Take a look at my VLOOKUPNEXT function in Thread68-693835 or search for VLOOKUPNEXT to see other variations on the theme.

It's VBA, but you don't have to do any coding yourself if you don't want to.

 

[sanscottfrd]

In the past I have concatenated multiple columns and performed the vlookup on the concatenation. Depending on the data, the concatenation is often a unique value.

Using your example, you would get values of:

1234510
12345123
1234567

 

[barbola]

Hey concatenate works great! thanks for the quick shortcut - I will use it alot.


Thanks!
Barb E.

 

[bluedragon2]

Concantenating data is a useful tool, but if you are able to use concantenate, then you should be able to use the SUMPRODUCT for your lookup without creating an extra column.

If you want, take a look at FAQ68-4725 to see how it could work for you.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[barbola]

I use sumproduct all the time, but in this case I was looking up a text value, not a sum of values.

thanks.

Thanks!
Barb E.