# formula to Check if cell value matches with Dynamic Row Number.

Copper Contributor

# formula to Check if cell value matches with Dynamic Row Number.

 I have Two Data Sheets"Sheet 1 (Raw Departures)" and "Sheet 2 (Raw Arrivals)" Sheet 1 (Raw Departures) Sheet 2 (Raw Arrivals) Guest Site# Guest Name Site# Match Site Row #. Sheet 1 Rrookie, Dan 3 Bell, Michael Steven 74 n/a Criss, Leon 5 Brown, Beth 18 n/a David, Clyde 129 Brown, Greg 64 n/a Evant, Tim 64 Celino, Darrell 153 n/a Smith, Jack 63 Cordova, Edgar 124 n/a Ham, Steve 151 Land, Danica 37 20 Site Does not Match Hakins, Sam 39 David, Clyde 129 13 Matched Site# Hookins, Shilo 18 Davis, Tina 107 n/a Kesslerl, Mikdew 131 Evant, Tim 121 14 Matched Site# Land, Danica 133 Hookins, Shilo 18 18 Matched Site# Using a Match() formula I have detected that "David, Clyde s in both sheets and the site number on Sheet 1 is in row 13. I now need to know if the site number is the same for both sheets; if "True" populate G12 with "Extended Stay" IF "FALSE" populate G12 with " Moved to New Site" How can I dynamically change the row # in a formula? Something like "= If("Sheet 1" Column B, row# (based on G"X","Extended Stay","Moved to New Site"" I need to change the row# based on the Row found in the Match() Any help is appreciated.
4 Replies

# Re: formula to Check if cell value matches with Dynamic Row Number.

See the attached workbook.

# Re: formula to Check if cell value matches with Dynamic Row Number.

Thank you for the quick response. I am new to getting in this deep and it will take a bit to digest this solution. Thank you for your suggestion.

# Re: formula to Check if cell value matches with Dynamic Row Number.

Thank you for introducing me to the LET()Function. It works great. However I have encountered another issue that I have spent countless hours of reading and watching videos to try and solve. I am not sure how to upload my worksheet so I have condensed it down to post it here. Any help or guidance what I should research would be greatly appreciated.

As before:
1) I have two separate Sheets
a) Date of Departure, b) Date of Arrival
2) The Guest name can be on the same sheet multiple times with different Site#
3) The Guest can be on the Departure sheet and be staying longer so they appear on the Arrival sheet as well.
a) If they remain in the same Site# they are considered an "Extension"
b) If they want to stay but have to move to a new site they are considered a "Move" and my sheet needs to read "Move from Site# x to Site# y.

I have narrowed the options down to this formula:
=LET(
ar_site,INDEX(\$F\$2:\$F\$201,MATCH(\$B2,\$E\$2:\$E\$201,0)),IFERROR(ar_site,""))

Where column
F - Index = Arrival Site#
B - Match = Departure Name
E - Match = Arrival Name

Issue:
If the Name appears multiple times my MATCH formula gives me the same row
number for each occurrence of the name and thereby the same Site# instead of the correct one. How can I get the correct row with multiple matches?

I will gladly upload my work if you cn direct me how to do so.