Look Up A Phone Number

See a robust formula that can handle any duplicate situation when doing a Reverse Two Way Lookup. See how to extract column and row headers when the lookup values are data inside the table and there are many duplicate lookup values. This Array Formula uses the functions, IF, OR, COLUMNS, INT, MOD, SMALL, INDEX, COLUMN, ROW, COLUMNS. This amazing trick comes from DonkeyOte and pgc01 at the Mr Excel Message Board. This video is an improvement on the formula as seen in video Excel Magic Trick #149.5: Reverse 2-Way Lookup w duplicates. The amazing concept in the video comes from the fact that we have duplicate lookup values and we must return the column and row headers in a specific order. The conceptual trick that will be used is to create a table of numbers in the same shape as the lookup table values and each number will contain both the column number and row number for the lookup value. We will multiply the column numbers by 10^5 then add the row numbers to get a single number that will have information about the column and row index numbers. Absolutely a must see conceptual trick if you want to master Reverse two way lookup! Reverse Two Way Lookup When Duplicates are Present.

Larry | Phone Number Reverse Look Up | 02 25th, 2011 |

10 Responses

1. ExcelIsFun Says:
   February 25th, 2011 at 9:33 pm

   Search for and watch this video title:

   excelisfun Search & Find Excel Videos, Playlists, Download Excel Workbooks

2. ahornek01 Says:
   February 25th, 2011 at 10:28 pm

   How do I download the excel file?

3. ExcelIsFun Says:
   February 25th, 2011 at 11:14 pm

   Thanks for the tip!!

4. Ifrite Says:
   February 26th, 2011 at 12:03 am

   The end of the MOD function should be Mod(…,10^5) instead of Mod(…/10^5,1)*10^5. Your formula is correct, but can become a little bit more efficient by doing so. : )

5. ExcelIsFun Says:
   February 26th, 2011 at 12:10 am

   I don’t understand…

   For date and time, see video 478

6. MrHelal123 Says:
   February 26th, 2011 at 12:43 am

   I guess if i was changing it to time using this formula, i would have to change the custom number format?

7. ExcelIsFun Says:
   February 26th, 2011 at 1:38 am

   Yes. But video 478 is an easier formula that works for dates and times only.

8. MrHelal123 Says:
   February 26th, 2011 at 2:32 am

   Will this formula work for dates and times

9. ExcelIsFun Says:
   February 26th, 2011 at 2:38 am

   It took me a long time to understand it also!!

10. mikeyroro Says:
    February 26th, 2011 at 3:38 am

    Wow! Talk about a mega-formula! I’m going to have to study this one for a bit…

Leave a Comment

Name

Mail (will not be published)

Website

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.