VLOOKUP fot multiple returns

**Tsantsa** · 07-29-2015, 12:01 PM

Hi, i have big table with 60 000 rows. Example is in attachment (do not worry i used just 20 rows in example).
pokusne vzorce.xlsx
My 1st goal is to find ID ref/mut (column E) is sheet SNPs and return ref and mut allele.
So far I worked with VLOOKUP and it was working for IDs with only one copy in sheet SNPs, problem is vlookup return only 1st value, so IDs with more copies in SNPs sheet are problematic.
Is there any way how i can find all alternatives?

p.s.
sorry for my gramar, I know i have terrible writen english

**ChemistB** · 07-29-2015, 12:33 PM

Okay, so in Z2 of Sheet1, you want to return the first found sequence matching E2 from SNPs columnn C (ref allele). In AA2, it will be first instance but Column D from SNPs (alt allele). Yes?

Then for the second instance that will happen (if there is one) in AB2 and AC2, and so on?

**Tsantsa** · 07-31-2015, 08:25 AM

Originally Posted by ChemistB

Okay, so in Z2 of Sheet1, you want to return the first found sequence matching E2 from SNPs columnn C (ref allele). In AA2, it will be first instance but Column D from SNPs (alt allele). Yes?

Then for the second instance that will happen (if there is one) in AB2 and AC2, and so on?

Yes, exactly.
Is there any way how to do it?

**ChemistB** · 07-31-2015, 09:26 AM

Here's what I think is the most efficient way
1. on SNPs, in F2 copied down
=A2&"_"&COUNTIF($A$2:$A2,A2)
This creates a unique ID for each sequence row. You can hide this column if you like

2. In Sheet1 Z2 copied to the right and down

=IFERROR(INDEX(SNPs!$C$2:$D$10002, MATCH($E2&"_"&INT((COLUMNS($A$1:A$1)-1)/2)+1,SNPs!$F$2:$F$10002,0),MOD(COLUMNS($A$1:A$1)-1,2)+1),"")

This finds the unique identifier from F and returns first column C, then D, then C, then D
Questions?