How to Reverse Lookup Numbers and Text In Excel

Reverse lookup of numbers is easier than that of texts.

Reverse Lookup Numbers

For example, how do you look up the appointment date and time for a patient in the below data?

The below video explains the steps

Note:

⭐ It is possible to create an array of Date and Time because Excel stores Date as integers and Time as a fraction of 24 hours

The issue becomes more complex when reverse-looking up text.

Reverse Lookup Text

For example, how do you return the Doctor & Session for a patient below?

See step by step video below

=INDEX(
Doctors&" - "&Sessions,
   MAX(IF(Clients=F5,ROW(Clients)-ROW(Sessions),"")),
      MAX(IF(Clients=F5,COLUMN(Clients)-COLUMN(Doctors),""))
   )

The trick here is using the MAX and IF functions to return both the Row and Column numbers

Also, when returning the row/column number, remember to adjust the count to start after the headers.

Using PowerQuery to Reverse Lookup

Here are the 5 steps

Step 1:
Get data from the Criteria table and load it as a connection only
Then get data from the Appointment table

Step 2:
Select the doctor’s column from the appointment table and unpivot other columns. Then filter it using any patient name.

Step 3:
Convert the criteria table to a single value by drilling down on the value

Step 4:
Replace the temporary filter with the criteria.

Step 5
Modify the filtered appointment table and load it out to an existing worksheet.

Using PowerQuery LeftOuter Join to Reverse Lookup

Here is an alternative using Left-Outer Join.

Step 1:
Get data from the Criteria table and load it as a connection only
Then get data from the Appointment table

Step 2:
Select the doctor’s column from the appointment table and unpivot other columns. Then filter it using any patient name.

Step 3:

Merge the Criteria with the appointment table where the criteria equal to the Patients

Step 4

Expand the merged query, remove the unnecessary columns and load it as a table

DOWNLOAD WORKSHEET

Read more

Linkedin Article

REVERSE LOOKUP IN EXCEL