Formula Help

I am trying to match a cell on our contract status report and pull that cell information into our recruitment sheet. On the contract sheet name is in the first column and contract status in the sixth column. I want the contract status to populate into a cell on the recruitment sheet by name.

I was thinking that VLOOKUP would work for this but now I am wondering if INDEX or MATCH would work? I have tried a couple different formulas and none of them work.

Thank you in advance

Find more posts tagged with

Healthcare

Formulas

Project Management

Comments

L_123

Index match is what you need as long as it is kept to a minimum. it is very memory intensive, so if you are referencing thousands of rows, it will make the sheet unstable.

We can't reference by column location, only by column name, so telling us the positions doesn't help. The formula would look something like this:

=index([range you want value returned from],match([value],[range to find value],0))

Paul Newcome

VLOOKUP would also work.

=VLOOKUP("Name", {Contract Sheet Range 1}, 6)

"Name" would be the value you are looking to match in the Contract sheet.

{Contract Sheet Range 1} is going to be the "table" you VLOOKUP is going to compare to within the Contract Sheet. Name Column being the first column (leftmost) and the Status Column being the last column (rightmost).

6 denotes which column number from your table you want the information to be displayed from.

See example below.

debra.martell37411

=INDEX([Contract Status Report Range 2],match([Physician Name],[Contract Status Report Range 1],0))

Above is the formula that I have been working with. I don't think that I am selecting the correct ranges/columns on the reference sheet. I need this formula to find the name on the reference sheet that corresponds with the name on the primary sheet and provide the corresponding contract status from the reference sheet.

The first range I am selecting is from the reference sheet (Columns 1, name). I have also tried selecting the first 6 columns that contain both the name and contract status.

For match I have tried selecting name column as well as contract status column.

For range I have tried the individual columns as well as selecting the range, name to contract status.

L_123

Your formula is correct in its format. The only issue I think there could be is with the ranges. You can only select one column for each of the 2 ranges (one for index, and one for match). They should both be the same size, and should have the same starting point in your situation. If you reference a matrix with either of these ranges it will pop an error.

L_123

take a look at this basic index match for when you build your ranges

https://app.smartsheet.com/b/publish?EQBCT=a826226af4ad4b8eb2b521ba20e63f09

Sign in to join the conversation!

Quick Links

Trending discussions

Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!
INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…