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

 

[BrettOlbrys1]

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

Just curious, why did you refer to the whole column in B:B, but limit it from 2 to 10000 in the others? Also, don't I need to add "$" to each variable other than X2 to ensure my lookup ranges don't change?

 

[Fluff]

why did you refer to the whole column in B:B, but limit it from 2 to 10000 in the others?

Just a matter of ease.

Also, don't I need to add "$" to each variable other than X2 to ensure my lookup ranges don't change?

If you are dragging the formula down, then yes.

 

[BrettOlbrys1]

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

I just noticed that there are actually some blanks where a sales code could be, could that cause this #NUM issue if there is a blank in the range? For example, the Sales Code columns could look like this:

P​	Q​	R​	S​
12345	23456		45678
11111	22222	33333	44444
98765		76543	65432

Even when I reference a specific row where I KNOW the sales code is listed in the DATA tab and I try to lookup that Sales code from my results tab, I still get the #NUM issue.

 

[BrettOlbrys1]

I just noticed that there are actually some blanks where a sales code could be, could that cause this #NUM issue if there is a blank in the range? For example, the Sales Code columns could look like this:

P​	Q​	R​	S​
12345	23456		45678
11111	22222	33333	44444
98765		76543	65432

Even when I reference a specific row where I KNOW the sales code is listed in the DATA tab and I try to lookup that Sales code from my results tab, I still get the #NUM issue.

I think the Sales Codes in the source table are formatted wrong because when I brought them outside the table and multiplied them by 1, and then referenced those new cells, the formula worked. Thanks for your collaboration to troubleshoot the issue!

 

[Fluff]

Glad it's sorted & thanks for the feedback.