Search in worksheet 2 (where data is stored) to find an exact side by side match for data in TWO cells not one. IF exact match Return (repeat) two ce

A

anne.murch

New Member
Joined
Sep 28, 2012
Messages
4
I really hope I can get an answer for this question. I've spent HOURS trying to figure this out!

I’M hoping that someone can help direct me. I’m able to search for an exact match using a VLOOKUP. I can only search one column though. <o:p></o:p>
<o:p> </o:p>
Here is my “current formula: <o:p></o:p>
<o:p> </o:p>
Match ONE cell and return (repeat) an exact match: <o:p></o:p>
=VLOOKUP(A2,StorageDB!$A:!A,1,FALSE) <o:p></o:p>
<o:p> </o:p>
If an exact match please provide me with the additional data:<o:p></o:p>
=IF(OR(EXACT(A2,B2))=TRUE,VLOOKUP(A2,StorageDB!$1:$1048576,2,FALSE))<o:p></o:p>
<o:p> </o:p>
The goal <o:p></o:p>
<o:p> </o:p>
I want to search TWO cells instead of the ONE. <o:p></o:p>
<o:p> </o:p>
To store information and when needed, I want to search for exact matches on two cells. I want to “prove” my search results by repeating my questions and adding the additional data that I might have in that row. As I said, I’ve been very successful in searching and repeating the first row using exactly this with ONE row. I can’t figure out how to search TWO and return TWO. <o:p></o:p>
<o:p> </o:p>
I have ONE workbook, and in that workbook I have two worksheets:<o:p></o:p>
1. Storage<o:p></o:p>
2. Worksheet<o:p></o:p>
<o:p> </o:p>
STORAGE - I have stored 100 Descriptions with their appropriate Questions as well as their status. <o:p></o:p>
· In the Description column there will be items that are repeated in that column.<o:p></o:p>
· In the Questions column there will be items that are repeated in that column. <o:p></o:p>
· In the Status column there will be items that are repeated in that column.<o:p></o:p>
<o:p> </o:p>
NOTE: BY ROW, the data (all three cells) in that row will be unique and therefore never repeated or duplicated. Here is a small example of what the Storage tab would like. <o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>​
A<o:p></o:p>​
B<o:p></o:p>​
C<o:p></o:p>​
1<o:p></o:p>
Description<o:p></o:p>​
Question<o:p></o:p>​
Status<o:p></o:p>​
2<o:p></o:p>
TR<o:p></o:p>
Department<o:p></o:p>
Current<o:p></o:p>
3<o:p></o:p>
PO<o:p></o:p>
Department<o:p></o:p>
Current<o:p></o:p>
4<o:p></o:p>
TR<o:p></o:p>
If employee works 26 consecutive pay periods with all hours at shift differential, all holiday and leave paid out at shift differential rate<o:p></o:p>
Current<o:p></o:p>
5<o:p></o:p>
TCM <o:p></o:p>
Apportion net investment activities and interest receipts against various funds and accounts based on user defined criteria<o:p></o:p>
I’m looking into this now. Will let you know.<o:p></o:p>
6<o:p></o:p>
HR<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
Current<o:p></o:p>
7<o:p></o:p>
PAY<o:p></o:p>
System allows employees to cancel their class registration on line<o:p></o:p>
Current<o:p></o:p>
8<o:p></o:p>
TREAS<o:p></o:p>
System notifies supervisor when employee cancels registration for required class.<o:p></o:p>
Current<o:p></o:p>
9<o:p></o:p>
FORMS<o:p></o:p>
System notifies enrollees via email when a class is modified or cancelled<o:p></o:p>
NOT Current<o:p></o:p>
10<o:p></o:p>
System <o:p></o:p>
Provide notification of upcoming investment payment dates<o:p></o:p>
NOT Current<o:p></o:p>
11<o:p></o:p>
System<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
Current<o:p></o:p>
12<o:p></o:p>
PAY<o:p></o:p>
System provides attendance rosters to division supervisors.<o:p></o:p>
Current<o:p></o:p>

<TBODY>
</TBODY>
<o:p> </o:p>
The Task<o:p></o:p>
<o:p> </o:p>
Step one – provide me questions - My boss has given me 80 Descriptions and Questions that he wants to see if I have in storage. Below is an example of the questions he would send to me. He wants to know if they exist and what is the status. <o:p></o:p>
<o:p> </o:p>​
A<o:p></o:p>​
B<o:p></o:p>​
1<o:p></o:p>
Description<o:p></o:p>​
Question<o:p></o:p>​
2<o:p></o:p>
PAY<o:p></o:p>
Department<o:p></o:p>
3<o:p></o:p>
PO<o:p></o:p>
Department<o:p></o:p>
4<o:p></o:p>
TR<o:p></o:p>
If employee works 45 consecutive pay periods with all hours at shift differential, all holiday and leave paid out at shift differential rate.<o:p></o:p>
5<o:p></o:p>
TCM <o:p></o:p>
Apportion net investment activities and interest receipts against various funds and accounts based on user defined criteria with the bunny rabbit.<o:p></o:p>
6<o:p></o:p>
HR<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
7<o:p></o:p>
PAY<o:p></o:p>
System allows employees to cancel their class registration on fire.<o:p></o:p>
8<o:p></o:p>
TREAS<o:p></o:p>
System notifies supervisor when employee cancels registration for required class.<o:p></o:p>
9<o:p></o:p>
FORMS<o:p></o:p>
System notifies enrollees via email when a class is modified or cancelled<o:p></o:p>
10<o:p></o:p>
System <o:p></o:p>
Provide notification of upcoming investment payment dates<o:p></o:p>
11<o:p></o:p>
System<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
12<o:p></o:p>
PAY<o:p></o:p>
System provides attendance rosters to division supervisors.<o:p></o:p>

<TBODY>
</TBODY>
<o:p> </o:p>
Step 2: I want add more functionality to my “Response” Workbook<o:p></o:p>
<o:p> </o:p>
· 1 - In the response worksheet, I want to “copy” the questions above and simply “paste” them into two columns (A & B). <o:p></o:p>
· 2 - Then I want the Description and the Question to “search” the Storage worksheet for exact matches. <o:p></o:p>
· 3 - I want to PROVE my exact results by: “REPEATING” both Description and Question as well all the “Status” data in that is in that row. <o:p></o:p>
· 4 – IF NOT AN EXACT MATCH – “return” “Not an exact match” in that row.<o:p></o:p>
<o:p> </o:p>
Note: I’ve created a sample of what my results look like (All highlighted in yellow)<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>​
A<o:p></o:p>​
B<o:p></o:p>​
C<o:p></o:p>​
D<o:p></o:p>​
E<o:p></o:p>​
1<o:p></o:p>
Description<o:p></o:p>​
(paste descriptions)<o:p></o:p>​
Question
(paste questions)<o:p></o:p>​
Description (results)<o:p></o:p>​
Question<o:p></o:p>​
(results)<o:p></o:p>​
Status<o:p></o:p>​
(results)<o:p></o:p>​
2<o:p></o:p>
PAY<o:p></o:p>
Department<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
3<o:p></o:p>
PO<o:p></o:p>
Department<o:p></o:p>
PO<o:p></o:p>
Department<o:p></o:p>
Current<o:p></o:p>
4<o:p></o:p>
TR<o:p></o:p>
If employee works 45 consecutive pay periods with all hours at shift differential, all holiday and leave paid out at shift differential rate.<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
5<o:p></o:p>
TCM <o:p></o:p>
Apportion net investment activities and interest receipts against various funds and accounts based on user defined criteria with the bunny rabbit.<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
6<o:p></o:p>
HR<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
HR<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
Current<o:p></o:p>
7<o:p></o:p>
PAY<o:p></o:p>
System allows employees to cancel their class registration on fire.<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
Not an exact match<o:p></o:p>
8<o:p></o:p>
TREAS<o:p></o:p>
System notifies supervisor when employee cancels registration for required class.<o:p></o:p>
TREAS<o:p></o:p>
System notifies supervisor when employee cancels registration for required class.<o:p></o:p>
Current<o:p></o:p>
9<o:p></o:p>
FORMS<o:p></o:p>
System notifies enrollees via email when a class is modified or cancelled<o:p></o:p>
FORMS<o:p></o:p>
System notifies enrollees via email when a class is modified or cancelled<o:p></o:p>
NOT Current<o:p></o:p>
10<o:p></o:p>
System <o:p></o:p>
Provide notification of upcoming investment payment dates<o:p></o:p>
System <o:p></o:p>
Provide notification of upcoming investment payment dates<o:p></o:p>
NOT Current<o:p></o:p>
11<o:p></o:p>
System<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
System<o:p></o:p>
System maintains waitlist for filled courses<o:p></o:p>
Current<o:p></o:p>
12<o:p></o:p>
PAY<o:p></o:p>
System provides attendance rosters to division supervisors.<o:p></o:p>
PAY<o:p></o:p>
System provides attendance rosters to division supervisors.<o:p></o:p>
Current<o:p></o:p>

<TBODY>
</TBODY>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
I got the Vlookup thing down if it were one column but I just can't figure out the two columns and exact matchs for those two to return. UG.. I feel like such a dummy. I've looked through so many posts and I can't find any similiar requests. Can someone direct me to a video or another post? Sigh...
 
Upvote 0
Hi Anne and welcome to Mr Excel Forum

Columns C (Description) and D (Question) in your table results are redundant.

So Put Status in C1 and in C2 this array formula

=IFERROR(INDEX(Storage!$C$2:$C$12,MATCH(1,IF(TRIM(Storage!$A$2:$A$12)=TRIM(A2),IF(TRIM(Storage!$B$2:$B$12)=TRIM(B2),1)),0)),"Not an exact match")

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

copy down

If you are unfamiliar with array formulas take a look at
Array Formulas

Hope this is what you need.

M.
 
Upvote 0
Anne,
Forgot to say: the formula above works in Excel 2007 or higher. What version of Excel are you using?

M.
 
Upvote 0
Marcelo,
First I'm so happy someoe replied that I'm jumping up and down! Thank you thank you thank you!

I'm using Excel 2010. I will try this right away!
 
Upvote 0
The formula should work fine in 2010
Dont forget to confirm the formula with Ctrl+Shift+Enter

And adjust the ranges - reviewing your original post i noticed that there are 100 items in Storage.
So try this

=IFERROR(INDEX(Storage!$C$2:$C$100,MATCH(1,IF(TRIM(Storage!$A$2:$A$100)=TRIM(A2),IF(TRIM(Storage!$B$2:$B$100)=TRIM(B2),1)), 0)),"Not an exact match")

Ctrl+Shift+Enter

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A
W
  • Question
How to Search for and return the last 12 matches of a value in either of two columns of a table in one worksheet and return those rows in a second wor
Replies
5
Views
883
widgewilliams
W
B
  • Question
Big Request, need help before I pull my hair out.
Replies
5
Views
1K
BrisAdrian
B
D
  • Question
Comparing items between two data sets
Replies
5
Views
681
hiker95
H

Forum statistics

Threads
1,214,827
Messages
6,121,816
Members
449,049
Latest member
cybersurfer5000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top