Formula Help

debra.martell37411

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

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