Can you make a formula lookup by number and name?

IBS1113

If employee only know name can they type in name and get project number and vice versa?

Jeff Reisman

@IBS1113

I would use a helper sheet with a report built from that sheet to create this lookup. Otherwise, you run into a lot of problems on the main sheet. The following will let you build a report where the user will have one row to use to lookup a name from a number, or a number from a name.

Create your helper sheet and add two columns (do not use the Primary Column!): Project Names and Project Numbers. Populate those columns with your data. Lock the columns.

Create two more columns, one called Project Name Entry and one called Project Number Lookup. In the first row of the Project Number Lookup column:

=INDEX([Project Numbers]:[Project Numbers], MATCH([Project Name Entry]@row, [Project Names]:[Project Names], 0))

Noe create two more columns, Project Number Entry and Project Name Lookup. In the first row of the Project Name Lookup column:

=INDEX([Project Names]:[Project Names], MATCH([Project Number Entry]@row, [Project Numbers]:[Project Numbers], 0))

Lock both Lookup columns, but not the Entry columns.

Now to test: Enter a Project Name value into the first row in the Project Name Entry column. The Project Number Lookup column should find the Project Number value. Vice versa with entering a Project Number and having the Project Name Lookup column return the Project Name value.

Once you know they work, we can wrap them in error proofing:

=IFERROR(INDEX([Project Numbers]:[Project Numbers], MATCH([Project Name Entry]@row, [Project Names]:[Project Names], 0)), "No match found. Please check the Project Name spelling.")

=IFERROR(INDEX([Project Names]:[Project Names], MATCH([Project Number Entry]@row, [Project Numbers]:[Project Numbers], 0)), "No match found. Please check the Project Number.")

Now to make this function correctly in a report format:

Create one more column called Include. Enter a 1 in this column on row 1 and and a 2 on row 2.

In Row 2, under Project Name Entry, type in: Enter Project Name above

In Row 2, under Project Number Entry, type in: Enter Project Number above

Set the text to be a size larger than normal and bold and/or a different color.

Ok, almost there:

Now create a report with your helper sheet as the data source. You want to add the two Entry columns and the two Lookup columns in the order of Name Entry, Number Lookup, Number Entry, Name Lookup. Also add the Include column. Set the filter to be where the Include column has any value, and sort by Include column (ascending.) You can then hide the Include column. Save.

When you open the report, you'll see two rows. When the users enters the Project Name in the Project Name Entry column above where it says "Enter Project Name above", they will get the Project Number returned in the next column, and so on with the Project Number Entry.