Row Number of the Source Sheet's Row, from Which the Current/Different Sheet's Cell Derived its Contents

[Turk182]

Good morning. I'm using Microsoft 365 on my Dell laptop .
I have the following formula in cell H9 Of sheet2, named "Test": '=TEXTJOIN(" -AND- ",TRUE,IF((Table3[City]=Test!H2)*(Table3[Primary NAICS Business Desc]=Test!H3),Table3[Address]&" "&Table3[City]&" "&Table3[ZIP Code],""))

In cell J9 of sheet2, named "Test", I would like a formula that uses H9 of sheet2, named "Test" (formula above) to give the row number Of the DIFFERENT sheet--(sheet1, named "Source"), from which the result in H9 sheet2, named "Test" was derived.

Thank you for reviewing.
Turk182

 

[Fluff]

Can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[shina67]

You can use the MATCH function combined with INDEX to find the row number of the result in the "Source" sheet.
Here's how you can do it:

=IF(H9<>"", INDEX(Source!A:A, MATCH(H9, TEXTJOIN(" -AND- ", TRUE, IF((Source!City=Test!H2)*(Source![Primary NAICS Business Desc]=Test!H3), Source!Address &" "& Source!City &" "& Source![ZIP Code], "")), 0)), "")

This formula checks if cell H9 on "Test" sheet is not empty. If it's not empty, it uses the MATCH function to find the row number of the result in the "Source" sheet based on the concatenated text in H9.
The INDEX function then retrieves the corresponding value from column A of the "Source" sheet.
Make sure to adjust the column reference in the INDEX function if the row number is in a different column.

 

[Fluff]

How do you expect the match function to work, when the 2nd argument is going to be a single string of text?

 

[shina67]

How do you expect the match function to work, when the 2nd argument is going to be a single string of text?

You're correct; my mistake. The MATCH function won't work directly with a single string of text as its lookup value. Instead, use the MATCH function within an array formula to search for the concatenated text within a range.

Try this corrected formula:

=IF(H9<>"", INDEX(Source!A:A, MATCH(1,(Source!A:A = H9), 0)),"")

This formula checks if cell H9 on the "Test" sheet is not empty. If it's not empty, it uses the INDEX and MATCH functions to find the row number of the result in the "Source" sheet based on the concatenated text in H9. The MATCH function searches for the exact match of the concatenated text within column A of the "Source" sheet. Then, the INDEX function retrieves the corresponding value from column A of the "Source" sheet. Adjust the column reference in the INDEX function if the row number is in a different column.

 

[Turk182]

Can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thx Fluff.
Unfortunately, my system still won't allow me to use XL2BB.
Like the last time I downloaded it, both "capture range” icons remain grayed out.

 

[Fluff]

Have a look here XL2BB Icons greyed out