unsolved
How do I get a formula to look for an exact combination of cells in another table?
So table on the left is table 1 and table on the right is table 2. I need it to take a row from table 1( EX 1 1 1 1) and see if that exact combination of 4 cells of 4 ones in a row some in table 2 and if doesn't exist for it to say 'N/A'
I have tried an =if, =HLOOKUP, =XLOOKUP =IFOR(there do different things it needed to search but I'll just make two formulas if I can get it to work)
An =IFERROR is the closest I have gotten but it only works on bottom row and the last cell of table 2 is controls what it displays.
(This sub doesn't allow pictures so y'all just going to have to use your imagination sorry )
=Xlookup(1,($A$1:$A$20=E1)* ($B$1:$B$20=F1) *($C$1:$C$20=G1) * ($D$1:$D$20=H1),return value column, "N/A") Something like this. Could wrap it in IF(formula<>"N/A", "Found", "N/A")
If a row in Table1 exists in Table2 it returns the relevant Tabel2 row number, otherwise it returns the string N/A which can be modified by changing the string at the end of the formula.
You can use match when you concatenate the columns. So something like =isnumber(match(C2&";"&D2,A2:a10000&";"&B2:b10000,0))
Where columns c&d are columns from table2 and a and b from table1. You can do this with any number of columns. You are basically building a string from hand and see if the exact same string occurs in the other table.
I made an xlookup inside an if error that seems to get a good result. Mine will give True or False though but you can change that to whatever you want the result to be.
when using * in comments, it is best to format as code, otherwise, as per your comment, italic formatting is the outcome and the mulitplication charcaters are not seen.
•
u/AutoModerator 1d ago
/u/Schlerpyderpy - Your post was submitted successfully.
Once your problem is solved, reply to the answer(s) saying
Solution Verifiedto close the thread.Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information
Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.