Index + Match function - not working properly.

Bert Hoffbeck

I've been using a simple index + match function to pull information from another sheet and for some reason on a new sheet the functionality seems to not work correctly. Specifically, it only correctly pulls the information for some of the rows.

I went and sorted the row in the source sheet and got it to work, but we'd prefer to keep the rows sorted differently. I understand when using VLookup that it's critical to have the information sorted in order to work, but haven't had an issue whenever using the Index + Match formulas until today:

Here are the two formulas I've used:

Formula A: =INDEX({HR - EE Master Email}, MATCH([EE#]@row, {HR - EE Master Range 1}), 0)

Formula B: =INDEX({HR - EE Master Email}, MATCH([EE#]@row, {HR - EE Master Range 1}), 1)

Bert Hoffbeck

Also, I'm not sure what the ,0 or ,1 at the end do...

parulmishra

@Bert Hoffbeck Here is the explanation on 0 and 1 in the Match formula.. Hope this helps

MATCH Function | Smartsheet Learning Center

https://help.smartsheet.com/function/match?_ga=2.6304818.1634072561.1685957751-445521271.1679386366&_gl=1*1ppsrgd*_ga*NDQ1NTIxMjcxLjE2NzkzODYzNjY.*_ga_ZYH7XNXMZK*MTY4NTk1Nzc1MS4xMDcuMS4xNjg1OTU4NTM1LjE4LjAuMA..

Returns the relative position of a value in a range (lookup table). The first position is 1.

Bert Hoffbeck

Thank you Parul.

Paul Newcome

Use the zero but move it into the MATCH function. Right now you have it as part of the INDEX function.

=INDEX({HR - EE Master Email}, MATCH([EE#]@row, {HR - EE Master Range 1}, 0))