Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Vlookup Similar Values

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have the list of data in two different worksheets.
First sheet is something like this

ANGKANH
KAB_PHLUK
PHUM_PRAMMUOY_DAB
KHNANG_TA_KONG

And second sheet is something like below

A41_2Gi_ANGKANH ID001
A41_2Gi_KAB_PHLUK ID002
A41_2Gi_PHUM_PRAMMUOY_DAB ID003
A41_2Gi_KHNANG_TA_KONG ID004

I would like to use vlookup to get ID from table2 to table1. Is it possible to ask excel to cheek if any values in the table1 are very much similar to the any values in the table2 then start vlookup?

Thanks you very much for your time. Your comment will be very much appreciated.


Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

Similar Topics







I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.

I've tried the following but got an error stating it was too long:

= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))

The repeating line needs attention in my mind:

MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))

Thanks


I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.

I've tried the following but got an error stating it was too long:

= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))

The repeating line needs attention in my mind:

MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))

Thanks


Hi All,

I am trying to create a formula for the following scenario but to no avail. Please note that Cell in Col A Sheet1 have multiple values. e.g A1 contains "ID001, ID002, ID003"

Sheet 1:
Col A Col B
ID001, ID002, ID003 Yes
ID004, ID005, ID006 No

Sheet 2:
Col A Col B
ID002 [Return Value = Yes]
ID006 [Return Value = No]


I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.

Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:

Code:

                      A                       B                      C                      D
1
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)


To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns B-D, but doing that gives me the result below:
Code:

                      A                       B                      C                      D
                      A                       B                      C                      D
1
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)


As you can see, I still have to go in to Columns B-D and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?


Hey Guys I Have I Q

I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work

Ex
1- Ranges Variables So I Can Choose The Range I Need
2- Look Up Table Variable So I Can Choose Between Tables I need
3- Color Every Range With Specific Color Through Drop Down List Color

Sheet1

  A B C D E F G H I J K L M N O P Q 1 A1 C 700     Range Tables Color   Table1   Table2   Table3 2 A2 C 701   First LookUp A1 A4 Table1 Blue Sky   A1 C 700   A1 Z 500   A1 Y 300 3 A3 C 702   Second LookUp A5 A13 Table3 Red   A2 C 701   A2 Z 501   A2 Y 301 4 A4 C 703   Third LookUp A14 A18 Table2 Yellow   A3 C 702   A3 Z 502   A3 Y 302 5 A5 Y 304               A4 C 703   A4 Z 503   A4 Y 303 6 A6 Y 305               A5 C 704   A5 Z 504   A5 Y 304 7 A7 Y 306               A6 C 705   A6 Z 505   A6 Y 305 8 A8 Y 307               A7 C 706   A7 Z 506   A7 Y 306 9 A9 Y 308               A8 C 707   A8 Z 507   A8 Y 307 10 A10 Y 309               A9 C 708   A9 Z 508   A9 Y 308 11 A11 Y 310               A10 C 709   A10 Z 509   A10 Y 309 12 A12 Y 311               A11 C 710   A11 Z 510   A11 Y 310 13 A13 Y 312               A12 C 711   A12 Z 511   A12 Y 311 14 A14 Z 513               A13 C 712   A13 Z 512   A13 Y 312 15 A15 Z 514               A14 C 713   A14 Z 513   A14 Y 313 16 A16 Z 515               A15 C 714   A15 Z 514   A15 Y 314 17 A17 Z 516               A16 C 715   A16 Z 515   A16 Y 315 18 A18 Z 517               A17 C 716   A17 Z 516   A17 Y 316 19                   A18 C 717   A18 Z 517   A18 Y 317 20                   A19 C 718   A19 Z 518   A19 Y 318 21                   A20 C 719   A20 Z519   A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)

Excel tables to the web >> Excel Jeanie HTML 4


Hi, I need a way to select a discontinuous range in VBA with these circumstances:

The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each

There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code

What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.

I can't seem to upload files here at work, but the tables look something like...

Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5

Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0

With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2

Thanks in advance, sorry for the mess


Here is my attempt.

=IF(A1="table1",VLOOKUP(B4,Matrix.xls!table1,2,1)*B6),IF(A1="table2",VLOOKUP(B4,Matrix.xls!table2,2,1)*B6),IF(A1="table3",VLOOKUP(B4,Matrix.xls!table3,2,1)*B6),IF(A1="table4",VLOOKUP(B4,Matrix.xls!table4,2,1)*B6),""))))

I have a validation in cell A1 with a dropdown list, table1,table2,table3,table4.

I want to be able to select that and then pull data from the matrix sheet based on the ranges with the same name using a vlookup returning the 2nd column.

Is this possible using this formula? Think I may be barking up the wrong tree here.

Thank you


I need help with a code so that if the lookup in W returns any value other than zero, it leaves X blank. If the lookup in W returns 0 then X=0

* W X 1 Tight Net Tight Net Rolls 2 3.25 5 3 2.25 3 4 0 0 5 0 0
Spreadsheet Formulas Cell Formula W2 =IF(VLOOKUP (C2,Table1,20,FALSE) ,VLOOKUP (C2,Table1,20,FALSE) ,0) W3 =IF(VLOOKUP (C3,Table1,20,FALSE) ,VLOOKUP (C3,Table1,20,FALSE) ,0) W4 =IF(VLOOKUP (C4,Table1,20,FALSE) ,VLOOKUP (C4,Table1,20,FALSE) ,0) X4 =IF(Table2[[#This Row],[Tight Net ]]=0,0,ISBLANK) W5 =IF(VLOOKUP (C5,Table1,20,FALSE) ,VLOOKUP (C5,Table1,20,FALSE) ,0)

Excel tables to the web >> Excel Jeanie HTML 4


I would like to code cell F3 so that if A3 & A4 are the same and D3 and D4 are the same then F3 is divided by 2 and F4= F3. If A3 is different from A4 and/or D3 is different from D4 then I do not want anything to happen to the value in F3

So using the example below if F3 were to entered as 58 being that A3=A4, D3 = D4 are all the same, F3 would be divided by 2 (equaling 29) and then F4 would equal the value of F3. Any suggestions???

* A B C D E F G H I J K L M N O P Q R 3 9-Jun 2 160 Jumbo TC * * * 2 0 50.9 0 2.5 0 0 3.5 x 42 * 0 PCS 4 9-Jun 3 160 Jumbo Alpina * * * 2 0 50.9 0 2.5 0 0 3.5 x 150 * 0 FT
Spreadsheet Formulas Cell Formula C3 =A3-DATE(YEAR (A3) ,1,0) D3 =VLOOKUP(B3,Table1,2,FALSE) E3 =VLOOKUP(B3,Table1,3,FALSE) I3 =VLOOKUP(B3,Table1,5,FALSE) J3 =F3*VLOOKUP(B3,Table1,7,FALSE) K3 =VLOOKUP(B3,Table1,7,FALSE) L3 =F3*VLOOKUP(B3,Table1,9,FALSE) M3 =VLOOKUP(B3,Table1,9,FALSE) N3 =F3*VLOOKUP(B3,Table1,11,FALSE) O3 =F3*VLOOKUP(B3,Table1,12,FALSE) P3 =VLOOKUP(B3,Table1,13,FALSE) R3 =Q3*VLOOKUP(B3,Table1,14,FALSE)& " " & VLOOKUP(B3,Table1,15,FALSE) C4 =A4-DATE(YEAR (A4) ,1,0) D4 =VLOOKUP(B4,Table1,2,FALSE) E4 =VLOOKUP(B4,Table1,3,FALSE) I4 =VLOOKUP(B4,Table1,5,FALSE) J4 =F4*VLOOKUP(B4,Table1,7,FALSE) K4 =VLOOKUP(B4,Table1,7,FALSE) L4 =F4*VLOOKUP(B4,Table1,9,FALSE) M4 =VLOOKUP(B4,Table1,9,FALSE) N4 =F4*VLOOKUP(B4,Table1,11,FALSE) O4 =F4*VLOOKUP(B4,Table1,12,FALSE) P4 =VLOOKUP(B4,Table1,13,FALSE) R4 =Q4*VLOOKUP(B4,Table1,14,FALSE)& " " & VLOOKUP(B4,Table1,15,FALSE)

Excel tables to the web >> Excel Jeanie HTML 4


SELECT Table1.Field4, [Table2].Field1, [Table2].Field2, [Table2].Field3
FROM Table1 INNER JOIN [Table2] ON Table1.Field4 = [Table2].Field4;



Thanks for any help. I'd like to be able to edit the display and have any
changes (or deletions) reflected in Table2.


Hi!
I have 3 tables, Table1 is in page 1.
Table2 and Table3 are in page 2.

Table2 gets a value from Table3 and Table1 gets a value from Table2.
However my problem is that no matter which values i input in Table3, it always show the value 0 in Table1.

If it matters, here are the formulas my tables use
Table1[Utgifter] = SUBTOTAL(109; Table2[Utgifter])
Table2[Utgifter] = SUBTOTAL(109; Table3[Utgifter])
Table3[Utgifter] = [a] - [b]

('a' and 'b' are row values in Table3).

So why do i always get the value 0 in Table1 and how can i fix this?

Thanks!


Hello All!

A newbie here. I appreciate any help. I am actually quite good in a # of areas - but not in VLookups. I basically have two tabs with similar lists of customers and misc info. One list has addresses and the other doesn't. Each customer has a customer # reflected on both tabbed page. My formula wld basically be: if customer # on tab 1 ='s customer # on tab 2 - take address from tab 1 and place in customer's row on tab 2.

Thanks 4 anyone helping me with this. I have already entitled the data in tab 1 (where the address is) "Table1."

A sample I was using to try to duplicate is as follows: (It however has a little more info in it which is confusing me) -
=IF(VLOOKUP(D3,Table1,1)=D3,IF(VLOOKUP(D3,Table1,2)=0,"DNP",VLOOKUP(D3,Table1,2)&IF(LEN(VLOOKUP(D3,Table1,2))=7,"","-"&VLOOKUP(C3,table2,2))),"Missing GL Number")

The CUST# is in Column B in Table 1 (1st Tab) and in Column E in the target table. The address is in column I. I realize I will be putting the VLookup formula in column I of the target table.

Also, if it is fairly easy if someone cld interpret the above sample - I'd be very grateful.

Again - thank you 4 taking the time to help. I really appreciate it. Marty


i create two table ,table1 contains following data.

Table1 item no customer invoice qty DM-1002 EVG 125 500 SS-7002 EVG 126 600 SO-MITB1 EVG 127 300 BUK-1005 BUK 128 300 BUK-1006 BUK 129 500
table2 contains following three headings of data

Table2 customer invoice qty

in table2 i want to get the customer name from first table so i create a simple relation between two tables.
i drag customer from table1 and leave it on table2 and i table2 design view i click customer heading and then in raw sorce i select table1 and in table1 it shows me drop down list of item no not for customer.

please tell me what's going wrong.

thanks.


Dear All,

I face the following problem:

table1:
________date1_date2_date3_...
material1_10____15____5____...
material2_5______8____4____...
material3...

table2:
_______date1_date2_date3
data1
material1
data2
material2

I'd like to put the values from table1 into table2 using searching criteria (reference): 'date' & 'material' (I tried vlookup but I'm searching for a better one)

I'd like to ask you to please help to solve this issue.

Thanks,
xx_yy


Hello,

I am new to excel and am struggling with vlookup. I have looked at various posts regarding vlookup but couldn't successfully use it to find a solution to my problem. The below is the description of my problem. I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.

A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15

I have another table (table2) in another sheet in the same workbook have a material and date.

A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008

My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.

I have tried using vlookup but it only works for the first match and doesn't check for other values

below is the function that i tried

=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")

Can anyone please help me with this problem.


Here is what I am trying to achieve.

In the attached Workbook, we are loading values from worksheets into one table. We are trying to sort the data into two tables, and have this update automatically based on a value in a cell.

When the sort is done, We would like to copy the whole row from table1 into table2, if certain conditions are met.

Should the variable change, have the row move from table2 back into table1.

The cell value needs to be less than 10 display into table2, if greater than 10, leave in table1.

Should the row move from one table to another, the row needs to be removed from the opposing table.

The cell values we are weighing in on are in the Column B...

Any advice will be greatly appreciated!!!

Greetings,

My first post so here goes. I'm trying to take a Table1 (6 columns x 10K rows) containing a range of values in each cell (ex -10 to 10) and I'd like to generate a copy of this table (Table2) that converts the value of each cell in the Table1 to a numeric value based on conditional formating. Each cell in Table2 needs to be populated by evaluating the parallel cell in Table1 according to the following format: If Table1 values is

Hi, all,

I need help!

I have Table1 and Table2 in Spreadsheet. In Table2, there are two or more Invoice Number under same Serial Number.
What I wnat is display the Invoice Number as Original Invoice Number in Table1 if Invoice Number in Table2 is different than Invoice Number in Table1 under same Serial Number.
I tried using Vlookup, but it just display the first value found.

Sample data is attached.

Please advise.


Hello!

I'm creating a sheet for a football league, I want that when I insert the results in table1, the table2 automatically fills up.

Is it possible? Should I change something in table1 first?

table1.jpg

table2.jpg

Thanks!


Hello everyone,

I have two tables, Table1- contains following fields: Item #, Warehouse #, Qty O/H, etc... and Table2- has Item #, Warehouse #, Qty sold, etc... Since, in Table1 and Table2, There are multiple line items for each Item # do to many warehouses.

Now, I need to pull the Qty Sold info. from Table2 into Table1 by matching Item # and warehouse for that Item #. I.e. Item # is in Column 1 and warehouse is in column 2 of Table 1 and same with Table2- the Item# is in Column1 and warehouse # is in column2 and Qty Sold is in Column 3.

Is this possible? Can you please help......

Thank you in advance.
Aman


Hello All,

I want to create a formular to return a value by searching a partial text. For example, in Sheet 2 col B, I want to search Sheet 2 col A (ID001) from the partial text of Sheet 1 Col A (ID001 - Name 1) and return the value of Sheet 1 Col B to Sheet 2 Col B (Yes).

Sheet 1:
Col A Col B
ID001 - Name 1 Yes
ID002 - Name 2 No
ID003 - Name 3 No

Sheet 2:
Col A Col B
ID001 [Return Value]
ID003 [Return Value]

Thanks for your help in advance.


Hello everyone,
I'm trying to build a command button or something there of that would allow me to switch list values.

I have the main sheet for data input, second sheet as two tables of different values, how can I make it so user can choose table1 or table2 to lookup the values?

Any pointers appreciated,
Bill


Hi

I have a relatively simple problem but not sure how to solve it.

I have two spreadsheets named Table1 and Table2.

I also have a list of loan numbers in Column A of Sheet 3.

I need a formula that will lookup each loan number in Table1, but if it is not there, then lookup Table2, and return the value found in Column B of either Table1 or Table2. I hope that is clear.

Any help would be apprecaited.

Rastus


I have 2~3 tables that share the same titles and different values. I want to change the color of font based on the value of another cell...it's confusing so I will explain with an example.

Table1

ColumnA(Title) ColumnB(Group1) ColumnC(Group2)
ABC 80 90
BCD 60 30
CDE 50 40
DEF 30 30
.
.
.


Table2

ColumnA(Title) ColumnB(Group1) ColumnC(Group2)
DEF 70 100
ABC 10 30
CDE 90 50
BCD 70 80


So what I want to do is IF ColumnB and ColumnC in Table 1 is less than 45, I want corresponsing numbers in Table 2 to be red. ColumnA in Table1 and Table2 has to match. The order of ColumnA in Table1 and the order of ColumnA in Table2 are different. I can't change this order to match them. It has to be the way it is. I can probably use IF, but I don't know what to do from there....

I have this formula "=IF(VLOOKUP($A67,Table1,1+1,0)<45,1,0)". I defined the first table as Table1 and the second table as Table2. Table1 starts at A13 and end at AN45. Table2 starts at A67 and end at AN99. Both Table1 and Table2 are filled with number(no empty cell). Column A consists of titles for table 1 and table2 (My table is placed top and bottom instead of side to side). I am not sure if column index is incorrect or something else is incorrect. I just set a cursor on an empty cell and entered this formula and clicked apply. All I get is #N?A.....WHY. If you could describe what you did on your spreadsheet I would aapreciate it. I suppose I need to use conditional formatting to change font color, but ....nothing worked....Anybody has an idea of how to fix it??

.
.
.


Hello, I'm having a time figuring this problem out.

My Setup:
Sheet1, Table1 is a multiple column table. Column1 is employee name and column 2 is employee location. There are many more columns, but those are all that is important for this problem.

Sheet2, Table2 is also a multiple column table. Column1 uses data validation to force the user to select an employee name from a drop-down. The employee name list is compiled using the data in Column1 of Table1. The rest of the columns contain data from vairous lookups, indexes, and other calculations.

Sheet2, Table3 is also a multiple column table. The rows within this table contain various types of charges, for example facilities.

My problem:
In column2 of Table3, i need to sum all of the data in Column2 of Table2 and then multiply by a factor. However, i only need to sum the data for employees who are in certain locations. For example, if employee A and B (Table2, Column1) are both in location 1 (Table1, Column2), then sum the data in Table2, Column2 and multiply by X.

What i've tried:
Multiple formulas, incuding sumif, sum(if), and index, including combinations. I can get this to work simply if i include an additional column in Sheet2, Table2 that does a vlookup of Sheet1, Table1, Column2 and get's the location for the employee name selected in Sheet2, Table2, Column1. However, i don't want to do it this way. I only want Sheet2, Table2 to contain the selected employee name in Column1 and then the other columns to provide the calculations i already have present and working. I do not want to duplicate the location information from Table1. (Besides, that would be too simple!)

Any help would be appreciated.