# Lookup multiple columns

1. ## Lookup multiple columns

I have a main telephone number lets say in Column A then in columns B to L I have all I have more telephone numbers, then finally in column M i have a Priority ID.

What i want to do is lookup the telephone number in column A and give me the Priority ID in column M if the telephone number is in either columns B,C,D,E,F,G,H,I,J,K,L

I can do a VLookup but this will only give me a result if the telephone number matches in the first column of the lookup, in this case column B.

Kind regards

David  Register To Reply

2. ## Re: Lookup multiple columns

Try this if you need the same phone no in all: =IF(COUNTIF(B2:L2,A2)>=11,M2,"One doesn't match")
If only one other match is needed: =IF(COUNTIF(B2:L2,A2)>=2,M2,"No match")  Register To Reply

3. ## Re: Lookup multiple columns

Formula maybe
=INDEX(M1:M11,AGGREGATE(15,6,ROW(A1:L11)/(1/(A1:L11="Tel_number")),1))

Or if you put a tel-number in N1 (instead direct in formula)
=INDEX(M1:M11,AGGREGATE(15,6,ROW(A1:L11)/(1/(A1:L11=N1)),1))

Regards.  Register To Reply

4. ## Re: Lookup multiple columns

Thanks, but this will only work if the matching data is on the same row, I want to lookup B1:L2000 and give me the result of column M where a match is found.

Sorry, I should have been clearer.

David  Register To Reply

5. ## Re: Lookup multiple columns

Did you mean the telephone number can be duplicated in others row? (So many of M will be reported)

Regards.  Register To Reply

6. ## Re: Lookup multiple columns

Hi

I suppose you want this.
Use this formula to get the ID in M3:M15 from the phone numbers in the table A3:L15, where D20 is the search phone number.
Formula:  `Please Login or Register  to view this content.`

See the file for clarification.  Register To Reply