Vlookup match two columns side-by-side with two columns side-by-side

[Healthcare Consultant]

I am not well-versed in excel formulas and am self-taught so, I have a hard time following some of the responses posted on here. I have Excel Version 2010.

I need a vlookup that will match two cells side-by-side against two other cells side-by-side. There are thousands of rows in both sheets, same file. I know the formula for a tradition vlookup but, in this case I need to match 2 for 2 and need help.

I can not combine the values with the concantenate function because the date cells will not format to match no matter what I do since they originated from access.

Please help me 'in lamens terms' with a formula that would do the following:

vlookup where lookup_value = '2 columns in table 1' and table_array = '2 columns in table 2', and col_index_num = 2 'from table 2'

Table 1<o></o>

5 Digit ID<o></o>	Date<o></o>
15001<o></o>	5/8/12<o></o>
15204<o></o>	2/5/12<o></o>
15204<o></o>	5/4/12<o></o>
15204<o></o>	6/7/12<o></o>
16000<o></o>	1/4/12<o></o>
16005<o></o>	5/7/12<o></o>
17007<o></o>	1/31/12<o></o>

<TBODY>
</TBODY>

Table 2<o></o>

5 Digit ID<o></o>	Date<o></o>
15154<o></o>	5/1/12<o></o>
15204<o></o>	2/5/12<o></o>
15204<o></o>	5/4/12<o></o>
15204<o></o>	7/4/12<o></o>
16044<o></o>	1/1/12<o></o>
16555<o></o>	1/4/12<o></o>
17007<o></o>	11/31/12<o></o>

<TBODY>
</TBODY>

<o></o>

 

[Healthcare Consultant]

Here are the results I am looking for:

Table 1

5 Digit ID	Date	Results
15001	5/8/12
15204	2/5/12	2/5/12
15204	5/4/12	5/4/12
15204	6/7/12
16000	1/4/12
16005	5/7/12
17007	1/31/12	1/31/12

<TBODY>
</TBODY>

 

[Aladin Akyurek]

Table 1 is located in A1:B8, including the headers.

Table 2 is located in E1:F8, including the headers.

C2, control+shift+enter, not just enter:

=IF(ISNUMBER(MATCH(1,IF($E$2:$E$8=A2,IF($F$2:$F$8=B2,1)),0)),B2,"")

 

[Dryver14]

Sheet1

	A	B	C	D
1	Table 1
2	5 Digit ID	Date
3	15001	05/08/2012
4	15204	02/05/2012
5	15204	05/04/2012
6	15204	06/07/2012
7	16000	01/04/2012
8	16005	05/07/2012
9	17007	1/31/12
10
11
12
13	Table 2
14	5 Digit ID	Date
15	15154	05/01/2012
16	15204	02/05/2012		02/05/2012
17	15204	05/04/2012		05/04/2012
18	15204	07/04/2012
19	16044	01/01/2012
20	16555	01/04/2012
21	17007	1/31/12		1/31/12

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 102px;"><col style="width: 64px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D15 =IF(A15&B15=A3&B3,B15,"") D16 =IF(A16&B16=A4&B4,B16,"") D17 =IF(A17&B17=A5&B5,B17,"") D18 =IF(A18&B18=A6&B6,B18,"") D19 =IF(A19&B19=A7&B7,B19,"") D20 =IF(A20&B20=A8&B8,B20,"") D21 =IF(A21&B21=A9&B9,B21,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Healthcare Consultant]

ADVERTISEMENT

Thank you, Aladin. I think this formula would've worked however, since I am not able to format B2 and F2 the same it may be why this is not working? B2 is formatted as 05/08/12 and F2 if formatted as 5/8/12. No matter what I do, I can not make the two consistent. I have tried converting text to columns for both, I have tried formatting cells by custom, date, text...nothing is working.

 

[Aladin Akyurek]

Thank you, Aladin. I think this formula would've worked however, since I am not able to format B2 and F2 the same it may be why this is not working? B2 is formatted as 05/08/12 and F2 if formatted as 5/8/12. No matter what I do, I can not make the two consistent. I have tried converting text to columns for both, I have tried formatting cells by custom, date, text...nothing is working.

Control+shift+enter, not just enter:

=IF(ISNUMBER(MATCH(1,IF($E$2:$E$8=A2,IF($F$2:$F$8+0=B2+0,1)),0)),B2+0,"")

Does this help?

 

[Healthcare Consultant]

Control+shift+enter, not just enter:

=IF(ISNUMBER(MATCH(1,IF($E$2:$E$8=A2,IF($F$2:$F$8+0=B2+0,1)),0)),B2+0,"")

Does this help?

Thank you, but I did Control+Shift+Enter and have the { brackets surrounding the formula as a result. It is still not working though. I thought that a requirement for vlookups to work successfully were that the comparing cells be of the same format?

 

[Aladin Akyurek]

Thank you, but I did Control+Shift+Enter and have the { brackets surrounding the formula as a result. It is still not working though. I thought that a requirement for vlookups to work successfully were that the comparing cells be of the same format?

We need to carry out some diagnostics...

Would you post the results that obtain with the following formulas:

=B2&""

=F2&""

=B2+0

=F2+0