Forum Discussion
Solved
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. |
Please test the attached version.
14 Replies
See the attached workbook.
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.
- 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.
Thank you in advance for your help.
