Looking for 2 values to get one match

[Dradeel]

Hello, first i would like to say that i love this forum. It has helped me a lot!

Ok so my problem is i'm trying to look up 2 values in Sheet 1 to get a match from another sheet. The two values i want to look up are the UserID and the Software, and the match i want is the Licence Key in Sheet 2.

Sheet 1 and 2 both have UserID and Software but only Sheet 2 has the Licence Key. Only problem for me is that Sheet 1 has 6000 entries and Sheet 2 has over 200k each column and it's quite time consuming trying to find the Licence Key individually.

Sheet 1 -
Machine Name | UserID | Location | Software |
ca6666 Bob01 Melbourne Microsoft Word
ba9999 Chris Perth Microsoft Excel

Sheet 2 -
Machine Name | UserID | Location | Manufacturer|Software | LicenceKey
ca6666 Bob01 Melbourne Microsoft Microsoft Word XXXX


Tried looking for one value but there are so many of the same that i need to lookup 2 values. Any ideas?

 

[evnoort]

Maybe this can help you?

http://www.cpearson.com/excel/FindAll.aspx

 

[Peter_SSs]

Hello, first i would like to say that i love this forum. It has helped me a lot!

Ok so my problem is i'm trying to look up 2 values in Sheet 1 to get a match from another sheet. The two values i want to look up are the UserID and the Software, and the match i want is the Licence Key in Sheet 2.

Sheet 1 and 2 both have UserID and Software but only Sheet 2 has the Licence Key. Only problem for me is that Sheet 1 has 6000 entries and Sheet 2 has over 200k each column and it's quite time consuming trying to find the Licence Key individually.

Sheet 1 -
Machine Name | UserID | Location | Software |
ca6666 Bob01 Melbourne Microsoft Word
ba9999 Chris Perth Microsoft Excel

Sheet 2 -
Machine Name | UserID | Location | Manufacturer|Software | LicenceKey
ca6666 Bob01 Melbourne Microsoft Microsoft Word XXXX


Tried looking for one value but there are so many of the same that i need to lookup 2 values. Any ideas?

Welcome to the MrExcel board!

See if this is what you mean. Copy the formula down the column.

Excel Workbook
	A	B	C	D	E
1	Machine Name	UserID	Location	Software	LicenceKey
2	ca6666	Bob01	Melbourne	Microsoft Word	XXXX
Sheet1

 

[Dradeel]

Morning Peter, I got a #REF error from this formula. Could you possibly explain the formula to me?

 

[Peter_SSs]

Could you possibly explain the formula to me?

The formula joins B2 & D2 (so in the example given previously 'Bob01Microsoft Word') then looks for this value in a join of columns B & E from Sheet2. The position of the match is used to return the corresponding value from column F of Sheet2.

I got a #REF error from this formula

Does column F of Sheet2 contain any #REF values?


Your sample data is very brief and difficult to be certain of columns etc. To see if we can get it working, and to help with explanation if more is required, set up the following in a new workbook.

Excel Workbook
	A	B	C	D	E	F
1	Machine Name	UserID	Location	Manufacturer	Software	LicenceKey
2	ba9999	Chris	Perth		Microsoft Excel	2365X
3	xy2365	Ben	Melbourne		Microsoft Word	H765TR
4	pp9876	Bob01	Melbourne		Microsoft Word	XXXX
5	ca6666	Bob01	Melbourne		Microsoft Excel	A326Y
6
Sheet2

Excel Workbook
	A	B	C	D	E
1	Machine Name	UserID	Location	Software	LicenceKey
2	ca6666	Bob01	Melbourne	Microsoft Word	XXXX
3	ba9999	Chris	Perth	Microsoft Excel	2365X
Sheet1

Copy the formula from E2 to E3.

Do I have the layout correct? If not, details please.
Are these the results you would expect for this data? If not, please explain.
Does it work for you? If not, explain what results you get, what you would expect, and why.

 

[Dradeel]

Hello Peter, the formula you did worked. There are so many values and only a few matches that i didn't even see them. Thanks a lot!!

 

[Peter_SSs]

Hello Peter, the formula you did worked. There are so many values and only a few matches that i didn't even see them. Thanks a lot!!

I'm not sure how that relates to any #REF errors, but I'm glad you have it working now.

Perhaps they were actually #N/A erors?
In any case, you could try this formula instead to see if it clears the 'clutter'.

=IFERROR(INDEX(Sheet2!F$1:F$5,MATCH(B2&D2,INDEX(Sheet2!B$1:B$5&Sheet2!E$1:E$5,0,1),0)),"")

 

[Dradeel]

I just had the wrong cells selected which caused the #REF error i believe. Thanks again mate!

 

[sixeyeco]

Wow. This is almost what I need. I hope it's not impolite to ask my question in here.

I have a data table named ITEMS_DATA with four columns and numerous rows

<style>table { }.font5 { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { font-size: 16pt; font-family: Calibri; }.xl25 { font-size: 16pt; font-family: Calibri; }ruby { }rt { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; display: none; }</style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="112" width="383"> <col style="" width="66"> <col style="" width="67"> <col style="" width="64"> <col style="" width="56"> <tbody><tr height="20"> <td class="xl24" height="20" width="66">ITEM</td> <td class="xl24" align="center" width="67">Gal</td> <td class="xl24" align="center" width="64">Qt</td> <td class="xl24" align="center" width="56">Pt</td> </tr> <tr height="20"> <td class="xl24" height="20">Beer1</td> <td class="xl25" align="center"> $30.00 </td> <td class="xl25" align="center"> $7.50 </td> <td class="xl25" align="center"> $3.75 </td> </tr> <tr height="20"> <td class="xl24" height="20">Beer2</td> <td class="xl25" align="center"> $37.50 </td> <td class="xl25" align="center"> $9.38 </td> <td class="xl25" align="center"> $4.69 </td> </tr> <tr height="20"> <td class="xl24" height="20">Beer3</td> <td class="xl25" align="center"> $45.00 </td> <td class="xl25" align="center"> $11.25 </td> <td class="xl25" align="center"> $5.63 </td> </tr> </tbody></table>
On another sheet I have 3 columns, the last of which contains a formula that you will hopefully construct for me

<style>table { }.font5 { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { font-size: 16pt; font-family: Calibri; }.xl25 { font-size: 16pt; font-family: Calibri; }.xl26 { font-size: 16pt; font-family: Calibri; }ruby { }rt { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; display: none; }</style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="112" width="295"> <col style="" width="66"> <col style="" width="67"> <col style="" width="64"> <tbody><tr height="20"> <td class="xl24" height="20" width="66">ITEM</td> <td class="xl24" align="center" width="67">Size</td> <td class="xl24" align="center" width="64">Cost</td> </tr> <tr height="20"> <td class="xl24" height="20">Beer1</td> <td class="xl25" align="center"> Pt </td> <td class="xl26" align="center">$3.75 </td> </tr> <tr height="20"> <td class="xl24" height="20">Beer2</td> <td class="xl25" align="center"> Gal </td> <td class="xl25" align="center"> $37.50 </td> </tr> <tr height="20"> <td class="xl24" height="20">Beer3</td> <td class="xl25" align="center"> Qt </td> <td class="xl25" align="center"> $11.25 </td> </tr> </tbody></table>
I did this once in my life already but I'm much older now and cannot find the answer. And the beer hasn't helped.

Thank you for any assistance you may provide.

 

[Aladin Akyurek]

Wow. This is almost what I need. I hope it's not impolite to ask my question in here.

I have a data table named ITEMS_DATA with four columns and numerous rows

<STYLE>table { }.font5 { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { font-size: 16pt; font-family: Calibri; }.xl25 { font-size: 16pt; font-family: Calibri; }ruby { }rt { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; display: none; }</STYLE><TABLE style="BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=383 height=112><COLGROUP><COL width=66><COL width=67><COL width=64><COL width=56><TBODY><TR height=20><TD class=xl24 height=20 width=66>ITEM</TD><TD class=xl24 width=67 align=center>Gal</TD><TD class=xl24 width=64 align=center>Qt</TD><TD class=xl24 width=56 align=center>Pt</TD></TR><TR height=20><TD class=xl24 height=20>Beer1</TD><TD class=xl25 align=center>$30.00 </TD><TD class=xl25 align=center>$7.50 </TD><TD class=xl25 align=center>$3.75 </TD></TR><TR height=20><TD class=xl24 height=20>Beer2</TD><TD class=xl25 align=center>$37.50 </TD><TD class=xl25 align=center>$9.38 </TD><TD class=xl25 align=center>$4.69 </TD></TR><TR height=20><TD class=xl24 height=20>Beer3</TD><TD class=xl25 align=center>$45.00 </TD><TD class=xl25 align=center>$11.25 </TD><TD class=xl25 align=center>$5.63 </TD></TR></TBODY></TABLE>
On another sheet I have 3 columns, the last of which contains a formula that you will hopefully construct for me

<STYLE>table { }.font5 { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { font-size: 16pt; font-family: Calibri; }.xl25 { font-size: 16pt; font-family: Calibri; }.xl26 { font-size: 16pt; font-family: Calibri; }ruby { }rt { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; display: none; }</STYLE><TABLE style="BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=295 height=112><COLGROUP><COL width=66><COL width=67><COL width=64><TBODY><TR height=20><TD class=xl24 height=20 width=66>ITEM</TD><TD class=xl24 width=67 align=center>Size</TD><TD class=xl24 width=64 align=center>Cost</TD></TR><TR height=20><TD class=xl24 height=20>Beer1</TD><TD class=xl25 align=center>Pt </TD><TD class=xl26 align=center>$3.75 </TD></TR><TR height=20><TD class=xl24 height=20>Beer2</TD><TD class=xl25 align=center>Gal </TD><TD class=xl25 align=center>$37.50 </TD></TR><TR height=20><TD class=xl24 height=20>Beer3</TD><TD class=xl25 align=center>Qt </TD><TD class=xl25 align=center>$11.25 </TD></TR></TBODY></TABLE>
I did this once in my life already but I'm much older now and cannot find the answer. And the beer hasn't helped.

Thank you for any assistance you may provide.

Let Sheet1, A1:D4 house the data, the headers included.

Let Sheet2 house the results range in A1:c4.

Sheet2

In C2 enter and copy down:

=INDEX(Sheet1!$B$2:$D$4,MATCH($A2,Sheet1!$A$2:$A$4,0),MATCH($B2,Sheet1!$B$1:$D$1,0))