Help with Index/Match formula

steve50951

I have a checkbox column "Inv 3rd Party Vendor." I am needing to index a master sheet with all of our property names, and look to see if the "inv 3rd party Vendor" box is checked in that sheet. If it is, I need it to check the box.

Screen Shot 2020-03-23 at 2.46.19 PM.png

Screen Shot 2020-03-23 at 2.46.48 PM.png

Paul Newcome

Is there data on each sheet that is unique to each row?

steve50951

yes. each row has a property name that is unique to that row.

Paul Newcome

Ok. Try something like this...

=INDEX({Other Sheet Checkbox Column}, MATCH([Property Name Column]@row, {Other Sheet Property Name Column}, 0))

steve50951

yes. each row has a property name that is unique to that row.

steve50951

works great!!! Thanks!

SGF

Paul Newcome

Happy to help! 👍️

steve50951

so invoice department added and additional column to index and match. They want to pull the "3rd Party Vendor" i copied the formula above and selected the column for the range, but its not quite returning the right info.

Here is the formula i am working with

=INDEX({CRS Vendor Setup Prop Name}, MATCH([Property Info]@row, {CRS Vendor Setup Vendor}))

But its returning the a value from the row above it. "Lumiere Chandler" instead of returning the value i want..."OPS"

Im missing something simple. Would you mind looking at it one more time?

Thanks

Paul Newcome

Try searching for an EXACT match with a zero at the end of your MATCH function....

=INDEX({CRS Vendor Setup Prop Name}, MATCH([Property Info]@row, {CRS Vendor Setup Vendor}, 0))

steve50951

comes back with a "No Match"

Paul Newcome

Is that exact text within the range on the source sheet?

steve50951

i need it return the value in the cell in the column "3rd Party Vendor Name" corresponding with "Madison at Round Grove" The value i need it to return is "OPS"

Am i using the wrong type of formula?

Paul Newcome

Yes. I understand what you need. I am just trying to help troubleshoot.

Try just the MATCH function.

=MATCH([Property Info]@row, {CRS Vendor Setup Vendor}, 0)

What happens?

steve50951

still returns a NO MATCH...hmm

steve50951

i figured it out.

=INDEX({CRS Vendor Setup Vendor Name}, MATCH([Property Info]@row, {CRS Vendor Setup Prop Name}, 0))

steve50951

swapped the range values in the index and match.