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

[anne.murch]

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></o>
<o> </o>
Here is my “current formula: <o></o>
<o> </o>
Match ONE cell and return (repeat) an exact match: <o></o>
=VLOOKUP(A2,StorageDB!$A:!A,1,FALSE) <o></o>
<o> </o>
If an exact match please provide me with the additional data:<o></o>
=IF(OR(EXACT(A2,B2))=TRUE,VLOOKUP(A2,StorageDB!$1:$1048576,2,FALSE))<o></o>
<o> </o>
The goal <o></o>
<o> </o>
I want to search TWO cells instead of the ONE. <o></o>
<o> </o>
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></o>
<o> </o>
I have ONE workbook, and in that workbook I have two worksheets:<o></o>
1. Storage<o></o>
2. Worksheet<o></o>
<o> </o>
STORAGE - I have stored 100 Descriptions with their appropriate Questions as well as their status. <o></o>
· In the Description column there will be items that are repeated in that column.<o></o>
· In the Questions column there will be items that are repeated in that column. <o></o>
· In the Status column there will be items that are repeated in that column.<o></o>
<o> </o>
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></o>
<o> </o>
[TABLE="class: MsoTableGrid, width: 494"]
<TBODY>[TR]
[TD="width: 34"]

<o> </o>​

[/TD]
[TD="width: 153"]

A<o></o>​

[/TD]
[TD="width: 435"]

B<o></o>​

[/TD]
[TD="width: 203"]

C<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 34"]1<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]

Description<o></o>​

[/TD]
[TD="width: 435, bgcolor: transparent"]

Question<o></o>​

[/TD]
[TD="width: 203, bgcolor: transparent"]

Status<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 34"]2<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]TR<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]Department<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]3<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]PO<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]Department<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]4<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]TR<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]If employee works 26 consecutive pay periods with all hours at shift differential, all holiday and leave paid out at shift differential rate<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]5<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]TCM <o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]Apportion net investment activities and interest receipts against various funds and accounts based on user defined criteria<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]I’m looking into this now. Will let you know.<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]6<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]HR<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]System maintains waitlist for filled courses<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]7<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]PAY<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]System allows employees to cancel their class registration on line<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]8<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]TREAS<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]System notifies supervisor when employee cancels registration for required class.<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]9<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]FORMS<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]System notifies enrollees via email when a class is modified or cancelled<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]NOT Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]10<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]System <o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]Provide notification of upcoming investment payment dates<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]NOT Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]11<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]System<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]System maintains waitlist for filled courses<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]12<o></o>
[/TD]
[TD="width: 153, bgcolor: transparent"]PAY<o></o>
[/TD]
[TD="width: 435, bgcolor: transparent"]System provides attendance rosters to division supervisors.<o></o>
[/TD]
[TD="width: 203, bgcolor: transparent"]Current<o></o>
[/TD]
[/TR]
</TBODY>[/TABLE]
<o> </o>
The Task<o></o>
<o> </o>
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></o>
[TABLE="class: MsoTableGrid, width: 491"]
<TBODY>[TR]
[TD="width: 34"]

<o> </o>​

[/TD]
[TD="width: 146"]

A<o></o>​

[/TD]
[TD="width: 640"]

B<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 34"]1<o></o>
[/TD]
[TD="width: 146, bgcolor: transparent"]

Description<o></o>​

[/TD]
[TD="width: 640, bgcolor: transparent"]

Question<o></o>​

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

<o> </o>​

[/TD]
[TD="width: 170"]

A<o></o>​

[/TD]
[TD="width: 187"]

B<o></o>​

[/TD]
[TD="width: 153"]

C<o></o>​

[/TD]
[TD="width: 150"]

D<o></o>​

[/TD]
[TD="width: 125"]

E<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 34"]1<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]

Description<o></o>​

(paste descriptions)<o></o>​

[/TD]
[TD="width: 187, bgcolor: transparent"]

Question
(paste questions)<o></o>​

[/TD]
[TD="width: 153"]

Description (results)<o></o>​

[/TD]
[TD="width: 150"]

Question<o></o>​

(results)<o></o>​

[/TD]
[TD="width: 125"]

Status<o></o>​

(results)<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 34"]2<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]PAY<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]Department<o></o>
[/TD]
[TD="width: 153"]Not an exact match<o></o>
[/TD]
[TD="width: 150"]Not an exact match<o></o>
[/TD]
[TD="width: 125"]Not an exact match<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]3<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]PO<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]Department<o></o>
[/TD]
[TD="width: 153"]PO<o></o>
[/TD]
[TD="width: 150"]Department<o></o>
[/TD]
[TD="width: 125"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]4<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]TR<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]If employee works 45 consecutive pay periods with all hours at shift differential, all holiday and leave paid out at shift differential rate.<o></o>
[/TD]
[TD="width: 153"]Not an exact match<o></o>
[/TD]
[TD="width: 150"]Not an exact match<o></o>
[/TD]
[TD="width: 125"]Not an exact match<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]5<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]TCM <o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]Apportion net investment activities and interest receipts against various funds and accounts based on user defined criteria with the bunny rabbit.<o></o>
[/TD]
[TD="width: 153"]Not an exact match<o></o>
[/TD]
[TD="width: 150"]Not an exact match<o></o>
[/TD]
[TD="width: 125"]Not an exact match<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]6<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]HR<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]System maintains waitlist for filled courses<o></o>
[/TD]
[TD="width: 153"]HR<o></o>
[/TD]
[TD="width: 150"]System maintains waitlist for filled courses<o></o>
[/TD]
[TD="width: 125"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]7<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]PAY<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]System allows employees to cancel their class registration on fire.<o></o>
[/TD]
[TD="width: 153"]Not an exact match<o></o>
[/TD]
[TD="width: 150"]Not an exact match<o></o>
[/TD]
[TD="width: 125"]Not an exact match<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]8<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]TREAS<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]System notifies supervisor when employee cancels registration for required class.<o></o>
[/TD]
[TD="width: 153"]TREAS<o></o>
[/TD]
[TD="width: 150"]System notifies supervisor when employee cancels registration for required class.<o></o>
[/TD]
[TD="width: 125"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]9<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]FORMS<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]System notifies enrollees via email when a class is modified or cancelled<o></o>
[/TD]
[TD="width: 153"]FORMS<o></o>
[/TD]
[TD="width: 150"]System notifies enrollees via email when a class is modified or cancelled<o></o>
[/TD]
[TD="width: 125"]NOT Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]10<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]System <o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]Provide notification of upcoming investment payment dates<o></o>
[/TD]
[TD="width: 153"]System <o></o>
[/TD]
[TD="width: 150"]Provide notification of upcoming investment payment dates<o></o>
[/TD]
[TD="width: 125"]NOT Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]11<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]System<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]System maintains waitlist for filled courses<o></o>
[/TD]
[TD="width: 153"]System<o></o>
[/TD]
[TD="width: 150"]System maintains waitlist for filled courses<o></o>
[/TD]
[TD="width: 125"]Current<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 34"]12<o></o>
[/TD]
[TD="width: 170, bgcolor: transparent"]PAY<o></o>
[/TD]
[TD="width: 187, bgcolor: transparent"]System provides attendance rosters to division supervisors.<o></o>
[/TD]
[TD="width: 153"]PAY<o></o>
[/TD]
[TD="width: 150"]System provides attendance rosters to division supervisors.<o></o>
[/TD]
[TD="width: 125"]Current<o></o>
[/TD]
[/TR]
</TBODY>[/TABLE]
<o> </o>
<o> </o>
<o> </o>

 

[anne.murch]

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...

 

[Marcelo Branco]

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.

 

[Marcelo Branco]

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

M.

 

[anne.murch]

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!

 

[Marcelo Branco]

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.

 

[anne.murch]

This is perfect! Thank you so much!!!!!!!!!!!!

 

[Marcelo Branco]

This is perfect! Thank you so much!!!!!!!!!!!!

You are very welcome and thanks for the feedback.

M.