VLOOKUP query

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
771
Office Version
  1. 365
Platform
  1. Windows
I have a query where i think a VLOOKUP would be best to search a table
The only issue i have is that i may need to search possibly one of two columns for the search.

Ie MGR & LOC are empty below but filled on master data table.

Is there any way i can use a vlookup to check against user id and if there isnt anything that matches MAZZAG it can search for Gavin Mazza.
In the master table these are in column A for user id and column B for Name


<TABLE style="WIDTH: 221pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=295 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=64>USER ID</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=103 x:str="NAME ">NAME </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 x:str="MGR ">MGR </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>LOC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>MAZZAG</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Gavin Mazza</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>
Gavin Mazza

Many Thanks
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Against which range do we match MAZZAG and against which range do we match Gavin Mazza? An what must happen when any of these actions is successful?
 
Upvote 0
Hi Gavin

I assume this setup.
<table valign="middle" colspan="10" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: bold; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="73,5pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="77,25pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="10" align="middle">Worksheet 'Sheet1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td><td align="middle">G</td><td align="middle">H</td><td align="middle">I</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">QUERY</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="left">MASTER</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td style="font-weight: normal;" align="left">User ID</td><td style="font-weight: normal;" align="left">Name</td><td style="font-weight: normal;" align="left">MGR</td><td style="font-weight: normal;" align="left">LOC</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="left">User ID</td><td style="font-weight: normal;" align="left">Name</td><td style="font-weight: normal;" align="left">MGR</td><td style="font-weight: normal;" align="left">LOC</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td style="font-weight: normal;" align="left">MAZZAG</td><td style="font-weight: normal;" align="left">Gavin Mazza</td><td style="font-weight: normal;" align="left">John</td><td style="font-weight: normal;" align="left">NY</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="left">MAZZAG0</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="left">John</td><td style="font-weight: normal;" align="left">NY</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="left">Gavin Mazza</td><td style="font-weight: normal;" align="left">John</td><td style="font-weight: normal;" align="left">NY</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>C3</td><td>=IFERROR(INDEX(H:H,MATCH($A3,$F:$F,0)),IFERROR(INDEX(H:H,MATCH($B3,$G:$G,0)),"no match"))</td></tr><tr><td>D3</td><td>=IFERROR(INDEX(I:I,MATCH($A3,$F:$F,0)),IFERROR(INDEX(I:I,MATCH($B3,$G:$G,0)),"no match"))</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>
 
Upvote 0
Will this check both in 1 sum or would they need to be done as seperate ones
 
Upvote 0
I need to be able to check in column 1 on say the front sheet, then natch ( Vlookup against the User Id, name on the main table to fill in the colums to the right?

Hope this makes sense
 
Upvote 0
gmazza76 said:
I need to be able to check in column 1 on say the front sheet, then natch ( Vlookup against the User Id, name on the main table to fill in the colums to the right?

Hope this makes sense
Click to expand...

Example...

MATCH(A2,E2:E20,0)

A2 is called a look up value.

In this formula, the look up value in A2 is matched against the range E2:E20 with 0 specifying an exact match.

Another example...

VLOOKUP(A2,E2:F20,2,0)

In this formula, the look up value A2 is matched against the range E2:E20 with 0 specifying an exact match and expecting a result from the range F2:F20, the 2nd column of the table E2:F20.

So I'll repeat the earlier question:

"Against which range do we match MAZZAG and against
which range do we match Gavin Mazza? An what must
happen when any of these actions is successful?"

When specifying a range, it would be convenient to mention also
the sheet which houses that range.
 
Upvote 0
I will try and expalin this best i can.

A report is generated for myself giving agent id's, name, agent manager, department and location.

For some reason say in column A can either have agent id ( mazzag ) or agent name ( gavin mazza ). I need to be able to use 1 table for the lookup, but i am unsure how to make the sum look at column A ( if id ) in the table ( Where id is kept ) or column B ( Where the name is ).

Does this help
 
Upvote 0
With a little change ...

<table valign="middle" colspan="9" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: bold; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="84,75pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="77,25pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="9" align="middle">Worksheet 'Sheet1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td><td align="middle">G</td><td align="middle">H</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">QUERY</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="left">MASTER</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td style="font-weight: normal;" align="left">User ID / Name</td><td style="font-weight: normal;" align="left">MGR</td><td style="font-weight: normal;" align="left">LOC</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="left">User ID</td><td style="font-weight: normal;" align="left">Name</td><td style="font-weight: normal;" align="left">MGR</td><td style="font-weight: normal;" align="left">LOC</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td style="font-weight: normal;" align="left">Gavin Mazza</td><td style="font-weight: normal;" align="left">John</td><td style="font-weight: normal;" align="left">NY</td><td style="font-weight: normal;" align="right">
</td><td style="font-weight: normal;" align="left">MAZZAG</td><td style="font-weight: normal;" align="left">Gavin Mazza</td><td style="font-weight: normal;" align="left">John</td><td style="font-weight: normal;" align="left">NY</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>B3</td><td>=IFERROR(INDEX(G:G,MATCH($A3,$E:$E,0)),IFERROR(INDEX(G:G,MATCH($A3,$F:$F,0)),"no match"))</td></tr><tr><td>C3</td><td>=IFERROR(INDEX(H:H,MATCH($A3,$E:$E,0)),IFERROR(INDEX(H:H,MATCH($A3,$F:$F,0)),"no match"))</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg
</td></tr></tbody></table>
 
Upvote 0
gmazza76 said:
I will try and expalin this best i can.

A report is generated for myself giving agent id's, name, agent manager, department and location.

For some reason say in column A can either have agent id ( mazzag ) or agent name ( gavin mazza ). I need to be able to use 1 table for the lookup, but i am unsure how to make the sum look at column A ( if id ) in the table ( Where id is kept ) or column B ( Where the name is ).

Does this help
Click to expand...

Since I failed to get the info in the format I wanted, I have to guess...

I'll assume a 2-column table the fomula must look at...

If the result you want to get is a text value:
Code: 
=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"Not Found",
     VLOOKUP(B2,Table,2,0),VLOOKUP(A2,Table,2,0)))

If the result you want to get is a numeric value:
Code: 
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},"Not Found",
     VLOOKUP(B2,Table,2,0),VLOOKUP(A2,Table,2,0)))

Does this get you what you have in mind?
 
Upvote 0
Aladin Akyurek said:
If the result you want to get is a numeric value:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},"Not Found",
VLOOKUP(B2,Table,2,0),VLOOKUP(A2,Table,2,0)))
Click to expand...
That will never return Not Found.

Maybe you meant:

=LOOKUP(1E100,CHOOSE({1,2,3},0,VLOOKUP(B2,table,2,0),VLOOKUP(A2,table,2,0)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,581
Messages
6,179,668
Members
452,936
Latest member
anamikabhargaw

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