Lookup a value across 4 columns?

[BrettOlbrys1]

This is a similar question to one I asked previously, but slightly different. I have a source file with 7,000 rows of data in each cell across columns A - Z. Each row corresponds to a specific person (therefore there are 7,000 employees). For each person, there is a unique sales code in column P, a different unique sales code in Q, a different unique sales code in R, and a different unique sales code in S (each person has 4 unique sales codes). Therefore, there are 28,000 unique sales codes in total.

When one of the 28,000 sales codes shows up in a report, I want to identify the Salesperson whose code was used. The Salesperson's name is in column B of the source file.

How do I look across columns P, Q, R, and S for a specific sales code and return the value in column B of that same row?

Thanks

Brett

 

[Herakles]

This is a similar question to one I asked previously, but slightly different. I have a source file with 7,000 rows of data in each cell across columns A - Z. Each row corresponds to a specific person (therefore there are 7,000 employees). For each person, there is a unique sales code in column P, a different unique sales code in Q, a different unique sales code in R, and a different unique sales code in S (each person has 4 unique sales codes). Therefore, there are 28,000 unique sales codes in total.

When one of the 28,000 sales codes shows up in a report, I want to identify the Salesperson whose code was used. The Salesperson's name is in column B of the source file.

How do I look across columns P, Q, R, and S for a specific sales code and return the value in column B of that same row?

Thanks

Brett

Where cell T2 contains the value to look up.

=OFFSET($B$1,(IFNA(MATCH($T$2,P:P,0),0)+IFNA(MATCH($T$2,Q:Q,0),0)+IFNA(MATCH($T$2,R:R,0),0)+IFNA(MATCH($T$2,S:S,0),0))-1,0)

 

[Fluff]

How about

=INDEX(B:B,AGGREGATE(15,6,ROW(B2:B10000)/(P2:S10000=X2),1))

Change X2 to the cell with the number you are looking for.

 

[BrettOlbrys1]

How about

=INDEX(B:B,AGGREGATE(15,6,ROW(B2:B10000)/(P2:S10000=X2),1))

Change X2 to the cell with the number you are looking for.

Fluff, thanks for the formula. Can you explain what each piece of the formula is doing because I modified it based on my actual set of data and it does not work and my file stops responding when the sheet tried to calculate the formula. One thing to note, my "data" is in a table and that is where the calculation is happening too.

My formula is:

=INDEX('DATA'!$B:$B,AGGREGATE(26,6,ROW('DATA'!$B$2:$B$10000)/('DATA'!$Z$2:$AC$10000=$I2),1))

I assumed the "15" in your original formula was referencing column P, so I changed it to "26" because my data actually starts in Z. What does the "6" do in the formula?

Thanks

Brett

 

[Dave Patton]

Check Excel's help for information on the Aggregate function. 15 is required.

 

[BrettOlbrys1]

Check Excel's help for information on the Aggregate function. 15 is required.

Thanks, stupid assumption on my part. I will rerun the formula to see if it works.

 

[BrettOlbrys1]

How about

=INDEX(B:B,AGGREGATE(15,6,ROW(B2:B10000)/(P2:S10000=X2),1))

Change X2 to the cell with the number you are looking for.

When I use your formula I get a NUM error.

 

[Fluff]

That suggests that there is nothing in columns P to S that matches the value in X2

 

[BrettOlbrys1]

That suggests that there is nothing in columns P to S that matches the value in X2

I know that isn't true because I did a find on the first number in my list and it found it in the data file.

Can you confirm these points for me:

- "B:B" refers to the column (in the DATA table) where the Sales reps names are listed and that name will be returned as the result
- "P2:S10000" refers to the columns (in the DATA table) where the Sales reps codes are listed
- "B2:B10000" refers to the column (in the DATA table) where the Sales reps names are listed
- "X2" is the column (in the RESULTS table) where the Sales codes are listed and where the resulting Sales reps names will also be listed once matched

Thanks

Brett

 

[Fluff]

That's right, although the names will be listed where the formula is, not in X2