0

I have an Excel (latest Windows 10 Office 365 version) workbook with 2 sheets. Sheet "Roster"has a column with last names. Sheet "CheckStatus" has a column with last names and the row number (using the match function) where the last name in it appears in the 1st sheet. I want to find the value of column B in the 1st sheet for the row number in the 1st sheet that appears in the 2nd sheet.

I've tried many different ways to use the ADDRESS function but cannot get it right (not even sure that's the best way to do it).

Improve this question
2
  • 1
    An example of what you have and desire would be very helpful here. Seems like a simple vlookup would work, but difficult to determine based on lack of data supplied.
    – Isolated
    Commented Mar 4, 2022 at 17:53
  • You should provide the sample about your requirement.
    – Lee
    Commented Mar 7, 2022 at 9:10

1 Answer 1

Reset to default
0

You are looking for the INDEX function, so MATCH will return the row number and INDEX will return the value at the row and column number specified.

Just keep in mind, if your data doesn't start at row 1, then both MATCH and INDEX will not return the row number, but rather the row index relative to the first row the data is in.

INDEX and MATCH form a powerful combination, and is quite helpful is you are doing multiple INDEX calls from a single MATCH row number, i.e. search once, look up multiple times. However, if you are doing only one search and one look up per row, you might as well use VLOOKUP or XLOOKUP (newer version Excel only). XLOOKUP is more flexible in terms of which column to search and from where to return, as well as the direction of searching (forwards or backwards).

Improve this answer
1
  • 1
    Thanks for the INDEX pointer. Got it working. I do have a sample workbook but don't see how to post it. The two formulas I used were: For the line number =IFERROR(MATCH(A2,(Roster!$F$2:$F$738),0),"") For the contents =IFERROR(INDEX(Roster,VALUE(E2),2),"")
    – SGretUSAFgeek
    Commented Mar 6, 2022 at 4:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .