Lookup multiple columns

**djfatboyfats** · 10-02-2019, 04:34 AM

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

**PaulM100** · 10-02-2019, 04:40 AM

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")

**menem** · 10-02-2019, 05:08 AM

(Sorry about my English)

If your data is A1:M11
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.

**djfatboyfats** · 10-02-2019, 05:13 AM

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

**menem** · 10-02-2019, 05:37 AM

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

Regards.

**José Augusto** · 10-02-2019, 05:42 AM

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.

Lookup multiple columns

LinkBack

Thread Tools

Rate This Thread

Display

Lookup multiple columns

Re: Lookup multiple columns

Re: Lookup multiple columns

Re: Lookup multiple columns

Re: Lookup multiple columns

Re: Lookup multiple columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Lookup multiple criteria in multiple columns in multiple rows; return true if exists

[SOLVED] Multiple Lookup Values rows and columns to lookup a single target column on the right end.

[SOLVED] VLookup - Single value lookup returning multiple records into multiple columns

Want to lookup multiple matching criteria and return multiple data in columns

[SOLVED] Multiple lookup adding columns, then multiplying, then lookup, add, and subtract

Lookup Data in One Column, Find Multiple Entries in Next, Copy to Multiple Columns

Lookup adjacent column to multiple lookup columns.

Bookmarks

Bookmarks

Posting Permissions