index match within one column

Amanda P

Is it possible to match a value in one sheet to a value in another sheet in a specific column and return the number in the row directly above? Please help if anyone can

Amanda P

@Leibel Shuchat

WoW!! so i am just getting back to this project and it worked!! thank you so much. i had tried the -1 but I was obviously doing something. when i followed your example it worked!!

Mike TV

@Amanda P

I believe the only way that this would be possible is if the cell above is a parent and you matched a child row. I couldn't locate a formula which would return a result of the cell location name which you'd need to know to be able to point it to the cell above.

Is there any data you can use to index match the cell above instead of index matching the cell below the one you're really after? It could be that you need to design your sheet around what you're trying to accomplish instead of looking for formulas for how you've designed your sheet.

Amanda P

Sorry i should have left these images to better explain. So i am hoping to search in one sheet for the value 49774 and in the end column return the number directly above it, so 49766 would populate. Is that possible?

HTMB HelP image.png

Paul Newcome

Are you able to provide a screenshot example of the desired outcome manually entered so we can see exactly what you are wanting to accomplish?

Mike TV

@Amanda P

I'm not sure how you would do that. I don't know how to index match to find a result and then tell SmartSheet "not that one, the cell directly above that result".

Leibel S

@Amanda P

Not positive this is what you need, but you could do just add a '-1' to the formula. See below example:

=INDEX({column you are looking up}, MATCH([GIFT CARD]@row, {Gift card Column}, 0) -1)

Mike TV

@Leibel Shuchat

Seems to work. Good job. I didn't know that was possible.

Amanda P

@Leibel Shuchat

WoW!! so i am just getting back to this project and it worked!! thank you so much. i had tried the -1 but I was obviously doing something. when i followed your example it worked!!