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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…