Email:      Pass:    Pass?
Advertisements


Free Excel Forum

If Or/and Statement

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

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


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
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note

Similar Topics







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


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


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


I am trying to setup a formula from cells that are set to a drop down list. On occasions I may need all the cells filled with a description to which I use a lookup with that adds to the lookup value in the cell below it and so on. However of 5 cells that I am adding lookup values toghether, certain occasions will require that only 2 of 5 cells be filled. When I do not select a description using the dropdown list and the cell is blank the formula does not work. Below is the code I started to write which works until a cell is entered that has nothing lookup, then things go south.

=((D14*VLOOKUP(H3,Table1,4,FALSE))*G3)/VLOOKUP(H3,Table1,3,FALSE)+((D14*VLOOKUP(H4,Table1,4,FALSE))*G4)/VLOOKUP(H4,Table1,3,FALSE)


You can see I am trying to add the results of several lookups together.  I run into problems when I get to a cell that does not have anything in it I get the N/A response.  What do I need to do so that it takes action when there is something to lookup and acts as 0 when the cell is empty???? 


I have this cell formula:
Code:

=if(countif(Table1,A1&"*")>1,"Need More",VLOOKUP(A1&"*",Table1,1,FALSE))


This code allows me to search for a record without having to place the entire record in cell A1. Also, if there is more than one record that starts with the given entry it will say "Need More".

However I would like to have the results of the entry if there are two possible records not just one.

For example, in cells A3 and A4 there would be formulas like this:
A3 = =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,First)),"N/A")

A4 =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,Second)),"N/A")

Did I explain that well enough. Please ask any questions. I am looking forward to this answer!


Thanks!
Nicole


Hi there, can anybody give me a better way of constructing this formula?

Code:

=SUM(VLOOKUP(4040,Table1[#All],16,FALSE)+VLOOKUP(1510,Table1[#All],16,FALSE))


I want to sum several vlookups and having to repeat the whole vlookup formula for each one seems inefficient to me.


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


hello!

i am trying to transfer data from one table to another using vlookup function.


If the check box is ticked, this means that this invoice has been paid and returns a TRUE; otherwise, it remains blank or simply indicates a FALSE.


This is the table I am trying to populate to summarize all outstanding items. Currently I am using a vlookup function to do this. The problem is if an invoice for the quarter has been paid in Table1, i have to manually delete the formula in Table2 so as not to show it as outstanding.

=IF(ISNA(VLOOKUP(A4,Sheet1!A4:Q6,2,0)),0,(VLOOKUP(A4,Sheet1!A4:Q6,2,0)))

I know I can use the TRUE, FALSE and BLANK status in Table1 to make this work more efficient. However, when I tried to do a multiple vlookup function nested in IF, it's not picking any numbers at all.

I thought I'd consult your guidance on this. Any assistance you can extend is truly appreciated.


-annais


How do I write this as one formula.

if(C1=1,(Vlookup(D8,Table1,2,False),"") OR
if(C1=2,(Vlookup(D8,Table2,2,False),"") OR
if(C1=3,(Vlookup(D8,Table3,2,False),"").

I have spent so much time trying to come up with correct syntax or formula with no success. Thank you very much.


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


Is there a way to block the #N/A type stuff in a field until someone puts a value to lookup into it? Right now I'm entering stuff into the 2nd column and the surrounding columns are using it to look up their data, but as you can see its pretty messy until someone puts something in the 2nd column.

Is there some if statement or something I can use to make it just stay blank until someone enters a badge number into the 2nd column?

Roster

* B C D E 3 E14 283 Pierce 651 4 T13 248 Kissinger 645 5 #N/A * #N/A #N/A
Spreadsheet Formulas Cell Formula B3 =VLOOKUP(C3,vlookup!$A$2:$F$86,2,FALSE) D3 =VLOOKUP(C3,vlookup!$A$2:$F$86,3,FALSE) E3 =VLOOKUP(C3,vlookup!$A$2:$F$86,4,FALSE) B4 =VLOOKUP(C4,vlookup!$A$2:$F$86,2,FALSE) D4 =VLOOKUP(C4,vlookup!$A$2:$F$86,3,FALSE) E4 =VLOOKUP(C4,vlookup!$A$2:$F$86,4,FALSE) B5 =VLOOKUP(C5,vlookup!$A$2:$F$86,2,FALSE) D5 =VLOOKUP(C5,vlookup!$A$2:$F$86,3,FALSE) E5 =VLOOKUP(C5,vlookup!$A$2:$F$86,4,FALSE)

Excel tables to the web >> Excel Jeanie HTML 4

Thanks! btw this excel html jeanie thing is nice


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 have a worksheet with the following formula

=-IF(ISNA(VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE)),0,VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE))

I have tried using shift + F5 to replace the following, but cannot get it to work

1) 'F:\My Documents with 'c:\My Documents

2) PERep3&4 with liverep3&4

It would be appreciated if you could assist

Howard


Good day,

I hope that I am not asking too much with this one.

I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.

I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!

What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.

How would I go about transferring/copying the following data into a userform?

Thanks

Ak

A1 B1 C1 D1 E1 P1 =TODAY() =NOW() A3 =COUNT(Master!A4:A301) Enter Product code below P N M E F S W S S G R A5 =VLOOKUP(C5,Master,7,FALSE) =VLOOKUP(C5,Master,8,FALSE) =VLOOKUP(C5,Master,9,FALSE) =VLOOKUP(C5,Master,10,FALSE) =VLOOKUP(C5,Master,11,FALSE) =VLOOKUP(C5,Master,12,FALSE) =VLOOKUP(C5,Master,13,FALSE) =VLOOKUP(C5,Master,14,FALSE) =VLOOKUP(C5,Master,15,FALSE) =VLOOKUP(C5,Master,16,FALSE) =VLOOKUP(C5,Master,17,FALSE) B M O M C M G H L S P A7 Product details =VLOOKUP(C5,Master,18,FALSE) =VLOOKUP(C5,Master,19,FALSE) =VLOOKUP(C5,Master,20,FALSE) =VLOOKUP(C5,Master,21,FALSE) =VLOOKUP(C5,Master,22,FALSE) =VLOOKUP(C5,Master,23,FALSE) =VLOOKUP(C5,Master,24,FALSE) =VLOOKUP(C5,Master,25,FALSE) =VLOOKUP(C5,Master,26,FALSE) =VLOOKUP(C5,Master,27,FALSE) =VLOOKUP(C5,Master,28,FALSE) Des A9 =IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE)) =VLOOKUP(C5,Master,29,FALSE) =VLOOKUP(C5,Master,30,FALSE) =VLOOKUP(C5,Master,31,FALSE) =VLOOKUP(C5,Master,32,FALSE) =VLOOKUP(C5,Master,33,FALSE) =VLOOKUP(C5,Master,34,FALSE) =VLOOKUP(C5,Master,35,FALSE) =VLOOKUP(C5,Master,36,FALSE) =VLOOKUP(C5,Master,37,FALSE) =VLOOKUP(C5,Master,38,FALSE) AL =VLOOKUP(C5,Master,39,FALSE) =VLOOKUP(C5,Master,40,FALSE) =VLOOKUP(C5,Master,41,FALSE) =VLOOKUP(C5,Master,42,FALSE) =VLOOKUP(C5,Master,43,FALSE) =VLOOKUP(C5,Master,44,FALSE) =VLOOKUP(C5,Master,45,FALSE) =VLOOKUP(C5,Master,46,FALSE) =VLOOKUP(C5,Master,47,FALSE) =VLOOKUP(C5,Master,48,FALSE) A11 =VLOOKUP(C5,Master,3,FALSE) =VLOOKUP(C5,Master,49,FALSE) =VLOOKUP(C5,Master,50,FALSE) =VLOOKUP(C5,Master,51,FALSE) =VLOOKUP(C5,Master,52,FALSE) =VLOOKUP(C5,Master,53,FALSE) =VLOOKUP(C5,Master,54,FALSE) =VLOOKUP(C5,Master,55,FALSE) =VLOOKUP(C5,Master,56,FALSE) =VLOOKUP(C5,Master,57,FALSE) =VLOOKUP(C5,Master,58,FALSE) Wa =VLOOKUP(C5,Master,59,FALSE) =VLOOKUP(C5,Master,60,FALSE) =VLOOKUP(C5,Master,61,FALSE) =VLOOKUP(C5,Master,62,FALSE) =VLOOKUP(C5,Master,63,FALSE) =VLOOKUP(C5,Master,64,FALSE) =VLOOKUP(C5,Master,65,FALSE) =VLOOKUP(C5,Master,66,FALSE) =VLOOKUP(C5,Master,67,FALSE) =VLOOKUP(C5,Master,68,FALSE) A13 =VLOOKUP(C5,Master,4,FALSE) =VLOOKUP(C5,Master,69,FALSE) =VLOOKUP(C5,Master,70,FALSE) =VLOOKUP(C5,Master,71,FALSE) =VLOOKUP(C5,Master,72,FALSE) =VLOOKUP(C5,Master,73,FALSE) =VLOOKUP(C5,Master,74,FALSE) =VLOOKUP(C5,Master,75,FALSE) =VLOOKUP(C5,Master,76,FALSE) =VLOOKUP(C5,Master,77,FALSE) =VLOOKUP(C5,Master,78,FALSE) Ar =VLOOKUP(C5,Master,79,FALSE) =VLOOKUP(C5,Master,80,FALSE) =VLOOKUP(C5,Master,81,FALSE) =VLOOKUP(C5,Master,82,FALSE) =VLOOKUP(C5,Master,83,FALSE) =VLOOKUP(C5,Master,84,FALSE) =VLOOKUP(C5,Master,85,FALSE) =VLOOKUP(C5,Master,86,FALSE) =VLOOKUP(C5,Master,87,FALSE) =VLOOKUP(C5,Master,88,FALSE) A15 =VLOOKUP(C5,Master,5,FALSE) =VLOOKUP(C5,Master,89,FALSE) =VLOOKUP(C5,Master,90,FALSE) =VLOOKUP(C5,Master,91,FALSE) =VLOOKUP(C5,Master,92,FALSE) =VLOOKUP(C5,Master,93,FALSE) =VLOOKUP(C5,Master,94,FALSE) =VLOOKUP(C5,Master,95,FALSE) A17 =COUNTA(Ingredients!A4:A200) =COUNTIF(Master!C4:C302,"Yes") Enter Part code below =(C19) Comprises A19 =VLOOKUP(C19,ing,3,FALSE) =VLOOKUP(C19,ing,4,FALSE) =VLOOKUP(C19,ing,5,FALSE) =VLOOKUP(C19,ing,6,FALSE) =VLOOKUP(C19,ing,7,FALSE) Des =VLOOKUP(C19,ing,8,FALSE) =VLOOKUP(C19,ing,9,FALSE) =VLOOKUP(C19,ing,10,FALSE) =VLOOKUP(C19,ing,11,FALSE) =VLOOKUP(C19,ing,12,FALSE) A21 =IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE)) =VLOOKUP(C19,ing,13,FALSE) =VLOOKUP(C19,ing,14,FALSE) =VLOOKUP(C19,ing,15,FALSE) =VLOOKUP(C19,ing,16,FALSE) =VLOOKUP(C19,ing,17,FALSE) A23 E23 P23


Hi All,

I am trying to add two Time values together from an imported report. Both formats are in HH:MM:SS. However I get a value error when columns b does not go over a an hour i.e only has a MM:SS value. Does anyone know how to get around this.

Secondly is there anyway to show values that are over 24 hours in a sum, i.e. row 24 should be 25 hours, 5 mins and 41 seconds but shows as 02:05:41.

Collection

I J K 23 3:51:57 :23:45 #VALUE! 24 22:34:27 03:31:14 02:05:41 25 4:39:24 :11:43 #VALUE! 26 6:29:11 :18:32 #VALUE! 27 11:17:56 02:57:12 14:15:08
Spreadsheet Formulas Cell Formula I23 =IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) ) J23 =IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) ) K23 =J23+I23 I24 =IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) ) J24 =IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) ) K24 =J24+I24 I25 =IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) ) J25 =IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) ) K25 =J25+I25 I26 =IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) ) J26 =IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) ) K26 =J26+I26 I27 =IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) ) J27 =IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) ) K27 =J27+I27

Excel tables to the web >> Excel Jeanie HTML 4


Below you'll see the long sheet. I highlighted the cells with issues. Seems my VLOOKUP is not pulling correct info. Help please.
Excel 2003 I J K L M N O P 2 TRIP NUMBER IN courier 3 GUA UPS Counts Trips Name 4 S27 UPS FedEx 0 10 United 5 113 NOW UPS 0 15 United 6 3 NOW NOW 0 20 United 7 134 NOW US MAIL 0 21 United 8 113 NOW Prestige 0 23 United 9 316 NOW #N/A 0 24 United 10 323 NOW 25 United 11 121 NOW 30 United 12 213 NOW 33 United 13 25 NOW 34 United 14 102 NOW 35 United 15 PAY #N/A 36 United 16 528 NOW 101 NOW 17 109 NOW 102 NOW 18 UAM UPS 103 NOW 19 47 NOW 104 NOW 20 AKR Premier 105 NOW 21 UAM UPS 106 NOW 22 UAM UPS 107 NOW 23 S27 UPS 108 NOW 24 30A NOW 109 NOW 25 25A NOW 110 NOW 26 AKR Premier 111 NOW 27 48 NOW 112 NOW 28 E26 USCARGO 113 NOW 29 FPM FedEx 114 NOW 30 CLE Premier 115 NOW 31 20 NOW 12 USMAIL 32 30A NOW 120 NOW 33 15 NOW 121 NOW 34 25A NOW 122 NOW 35 CLE Premier 123 NOW 36 34 NOW 124 NOW 37 44 NOW 125 NOW 38 33 NOW 126 NOW 39 24 NOW 127 NOW 40 CLE Premier 130 NOW 41 24 NOW 131 NOW 42 33 NOW 134 NOW 43 CLE Premier 135 NOW 44 FAM FedEx 136 NOW 45 FAM FedEx 137 NOW 46 UAM UPS 138 NOW 47 30A NOW 139 NOW 48 S27 UPS 140 NOW 49 FAM FedEx 143 NOW 50 CLE Premier 144 NOW 51 S27 UPS 145 NOW 52 FAM FedEx 15A United 53 323 NOW 202 NOW 54 COL Premier 203 NOW 55 CLE Premier 204 NOW 56 CLE Premier 205 NOW 57 GUA UPS 206 NOW 58 106 NOW 207 NOW 59 MUP #N/A 208 NOW 60 CLE Premier 209 NOW 61 PAY #N/A 210 NOW 62 CLE Premier 211 NOW 63 CLE Premier 212 NOW 64 AKR Premier 213 NOW 65 214 NOW 214 NOW 66 PAY #N/A 21A United 67 PAY #N/A 25A United 68 FAM FedEx 26 USCARGO 69 GUA UPS 301 NOW 70 CLE Premier 302 NOW 71 CLE Premier 303 NOW 72 FAM FedEx 304 NOW 73 23 NOW 305 NOW 74 CLE Premier 306 NOW 75 COL Premier 307 NOW 76 S27 UPS 309 NOW 77 CLE Premier 30A United 78 COL Premier 310 NOW Oct2010

Worksheet Formulas Cell Formula J3 =VLOOKUP( LEFT( I3 )&"*",$O$3:$P$104,2,FALSE ) J4 =VLOOKUP( LEFT( I4 )&"*",$O$3:$P$104,2,FALSE ) J5 =VLOOKUP( LEFT( I5 )&"*",$O$3:$P$104,2,FALSE ) J6 =VLOOKUP( LEFT( I6 )&"*",$O$3:$P$104,2,FALSE ) J7 =VLOOKUP( LEFT( I7 )&"*",$O$3:$P$104,2,FALSE ) J8 =VLOOKUP( LEFT( I8 )&"*",$O$3:$P$104,2,FALSE ) J9 =VLOOKUP( LEFT( I9 )&"*",$O$3:$P$104,2,FALSE ) J10 =VLOOKUP( LEFT( I10 )&"*",$O$3:$P$104,2,FALSE ) J11 =VLOOKUP( LEFT( I11 )&"*",$O$3:$P$104,2,FALSE ) J12 =VLOOKUP( LEFT( I12 )&"*",$O$3:$P$104,2,FALSE ) J13 =VLOOKUP( LEFT( I13 )&"*",$O$3:$P$104,2,FALSE ) J14 =VLOOKUP( LEFT( I14 )&"*",$O$3:$P$104,2,FALSE ) J15 =VLOOKUP( LEFT( I15 )&"*",$O$3:$P$104,2,FALSE ) J16 =VLOOKUP( LEFT( I16 )&"*",$O$3:$P$104,2,FALSE ) J17 =VLOOKUP( LEFT( I17 )&"*",$O$3:$P$104,2,FALSE ) J18 =VLOOKUP( LEFT( I18 )&"*",$O$3:$P$104,2,FALSE ) J19 =VLOOKUP( LEFT( I19 )&"*",$O$3:$P$104,2,FALSE ) J20 =VLOOKUP( LEFT( I20 )&"*",$O$3:$P$104,2,FALSE ) J21 =VLOOKUP( LEFT( I21 )&"*",$O$3:$P$104,2,FALSE ) J22 =VLOOKUP( LEFT( I22 )&"*",$O$3:$P$104,2,FALSE ) J23 =VLOOKUP( LEFT( I23 )&"*",$O$3:$P$104,2,FALSE ) J24 =VLOOKUP( LEFT( I24 )&"*",$O$3:$P$104,2,FALSE ) J25 =VLOOKUP( LEFT( I25 )&"*",$O$3:$P$104,2,FALSE ) J26 =VLOOKUP( LEFT( I26 )&"*",$O$3:$P$104,2,FALSE ) J27 =VLOOKUP( LEFT( I27 )&"*",$O$3:$P$104,2,FALSE ) J28 =VLOOKUP( LEFT( I28 )&"*",$O$3:$P$104,2,FALSE ) J29 =VLOOKUP( LEFT( I29 )&"*",$O$3:$P$104,2,FALSE ) J30 =VLOOKUP( LEFT( I30 )&"*",$O$3:$P$104,2,FALSE ) J31 =VLOOKUP( LEFT( I31 )&"*",$O$3:$P$104,2,FALSE ) J32 =VLOOKUP( LEFT( I32 )&"*",$O$3:$P$104,2,FALSE ) J33 =VLOOKUP( LEFT( I33 )&"*",$O$3:$P$104,2,FALSE ) J34 =VLOOKUP( LEFT( I34 )&"*",$O$3:$P$104,2,FALSE ) J35 =VLOOKUP( LEFT( I35 )&"*",$O$3:$P$104,2,FALSE ) J36 =VLOOKUP( LEFT( I36 )&"*",$O$3:$P$104,2,FALSE ) J37 =VLOOKUP( LEFT( I37 )&"*",$O$3:$P$104,2,FALSE ) J38 =VLOOKUP( LEFT( I38 )&"*",$O$3:$P$104,2,FALSE ) J39 =VLOOKUP( LEFT( I39 )&"*",$O$3:$P$104,2,FALSE ) J40 =VLOOKUP( LEFT( I40 )&"*",$O$3:$P$104,2,FALSE ) J41 =VLOOKUP( LEFT( I41 )&"*",$O$3:$P$104,2,FALSE ) J42 =VLOOKUP( LEFT( I42 )&"*",$O$3:$P$104,2,FALSE ) J43 =VLOOKUP( LEFT( I43 )&"*",$O$3:$P$104,2,FALSE ) J44 =VLOOKUP( LEFT( I44 )&"*",$O$3:$P$104,2,FALSE ) J45 =VLOOKUP( LEFT( I45 )&"*",$O$3:$P$104,2,FALSE ) J46 =VLOOKUP( LEFT( I46 )&"*",$O$3:$P$104,2,FALSE ) J47 =VLOOKUP( LEFT( I47 )&"*",$O$3:$P$104,2,FALSE ) J48 =VLOOKUP( LEFT( I48 )&"*",$O$3:$P$104,2,FALSE ) J49 =VLOOKUP( LEFT( I49 )&"*",$O$3:$P$104,2,FALSE ) J50 =VLOOKUP( LEFT( I50 )&"*",$O$3:$P$104,2,FALSE ) J51 =VLOOKUP( LEFT( I51 )&"*",$O$3:$P$104,2,FALSE ) J52 =VLOOKUP( LEFT( I52 )&"*",$O$3:$P$104,2,FALSE ) J53 =VLOOKUP( LEFT( I53 )&"*",$O$3:$P$104,2,FALSE ) J54 =VLOOKUP( LEFT( I54 )&"*",$O$3:$P$104,2,FALSE ) J55 =VLOOKUP( LEFT( I55 )&"*",$O$3:$P$104,2,FALSE ) J56 =VLOOKUP( LEFT( I56 )&"*",$O$3:$P$104,2,FALSE ) J57 =VLOOKUP( LEFT( I57 )&"*",$O$3:$P$104,2,FALSE ) J58 =VLOOKUP( LEFT( I58 )&"*",$O$3:$P$104,2,FALSE ) J59 =VLOOKUP( LEFT( I59 )&"*",$O$3:$P$104,2,FALSE ) J60 =VLOOKUP( LEFT( I60 )&"*",$O$3:$P$104,2,FALSE ) J61 =VLOOKUP( LEFT( I61 )&"*",$O$3:$P$104,2,FALSE ) J62 =VLOOKUP( LEFT( I62 )&"*",$O$3:$P$104,2,FALSE ) J63 =VLOOKUP( LEFT( I63 )&"*",$O$3:$P$104,2,FALSE ) J64 =VLOOKUP( LEFT( I64 )&"*",$O$3:$P$104,2,FALSE ) J65 =VLOOKUP( LEFT( I65 )&"*",$O$3:$P$104,2,FALSE ) J66 =VLOOKUP( LEFT( I66 )&"*",$O$3:$P$104,2,FALSE ) J67 =VLOOKUP( LEFT( I67 )&"*",$O$3:$P$104,2,FALSE ) J68 =VLOOKUP( LEFT( I68 )&"*",$O$3:$P$104,2,FALSE ) J69 =VLOOKUP( LEFT( I69 )&"*",$O$3:$P$104,2,FALSE ) J70 =VLOOKUP( LEFT( I70 )&"*",$O$3:$P$104,2,FALSE ) J71 =VLOOKUP( LEFT( I71 )&"*",$O$3:$P$104,2,FALSE ) J72 =VLOOKUP( LEFT( I72 )&"*",$O$3:$P$104,2,FALSE ) J73 =VLOOKUP( LEFT( I73 )&"*",$O$3:$P$104,2,FALSE ) J74 =VLOOKUP( LEFT( I74 )&"*",$O$3:$P$104,2,FALSE ) J75 =VLOOKUP( LEFT( I75 )&"*",$O$3:$P$104,2,FALSE ) J76 =VLOOKUP( LEFT( I76 )&"*",$O$3:$P$104,2,FALSE ) J77 =VLOOKUP( LEFT( I77 )&"*",$O$3:$P$104,2,FALSE ) J78 =VLOOKUP( LEFT( I78 )&"*",$O$3:$P$104,2,FALSE )



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.


I apologize for the double post but I may not have been clear in my earlier one. I also wanted to include an example.

When using time values, formatted as Custom>h:mm, in a vlookup statement I get a not available error. If I type in the value or copy and paste it in the vlookup will work. I am not sure if it is the function I am using to subtract one minute or it is a formatting error.

This is what it looks like (missing in the formulas is D2 =A2):
Sheet1

  A B C D E 1 Time Price ($)   Time Price ($) 2 22:44 49.66   22:44 49.66 3 22:43 50.66   22:43 #N/A 4 22:41 52.05   22:42 #N/A 5 22:35 52.07   22:41 #N/A 6 22:33 54.06   22:40 #N/A 7 22:29 54.43   22:39 #N/A 8 22:18 69.66   22:38 #N/A 9 22:00 73.01   22:37 #N/A 10 21:47 73.01   22:36 #N/A 11 21:41 75.82   22:35 #N/A 12 21:00 83.9   22:34 #N/A 13 20:18 75.82   22:33 #N/A 14 20:03 73.01   22:32 #N/A 15 20:00 69.66   22:31 #N/A 16 19:22 69.66   22:30 #N/A 17 19:00 73.01   22:29 #N/A
Spreadsheet Formulas Cell Formula E2 =VLOOKUP(D2,$A$2:$B$17,2,FALSE) D3 =D2-TIME(0,1,0) E3 =VLOOKUP(D3,$A$2:$B$17,2,FALSE) D4 =D3-TIME(0,1,0) E4 =VLOOKUP(D4,$A$2:$B$17,2,FALSE) D5 =D4-TIME(0,1,0) E5 =VLOOKUP(D5,$A$2:$B$17,2,FALSE) D6 =D5-TIME(0,1,0) E6 =VLOOKUP(D6,$A$2:$B$17,2,FALSE) D7 =D6-TIME(0,1,0) E7 =VLOOKUP(D7,$A$2:$B$17,2,FALSE) D8 =D7-TIME(0,1,0) E8 =VLOOKUP(D8,$A$2:$B$17,2,FALSE) D9 =D8-TIME(0,1,0) E9 =VLOOKUP(D9,$A$2:$B$17,2,FALSE) D10 =D9-TIME(0,1,0) E10 =VLOOKUP(D10,$A$2:$B$17,2,FALSE) D11 =D10-TIME(0,1,0) E11 =VLOOKUP(D11,$A$2:$B$17,2,FALSE) D12 =D11-TIME(0,1,0) E12 =VLOOKUP(D12,$A$2:$B$17,2,FALSE) D13 =D12-TIME(0,1,0) E13 =VLOOKUP(D13,$A$2:$B$17,2,FALSE) D14 =D13-TIME(0,1,0) E14 =VLOOKUP(D14,$A$2:$B$17,2,FALSE) D15 =D14-TIME(0,1,0) E15 =VLOOKUP(D15,$A$2:$B$17,2,FALSE) D16 =D15-TIME(0,1,0) E16 =VLOOKUP(D16,$A$2:$B$17,2,FALSE) D17 =D16-TIME(0,1,0) E17 =VLOOKUP(D17,$A$2:$B$17,2,FALSE)


Thank you.


Hello everyone, its been a while since my last post but after searching the boards for hours, I give up and need to ask for some help. Thanks to all who have posted and answered, its always a great help! I am sorry if I dont fully explain myself, I may be a little "punch drunk" fm searching for so long..... here is my delima.....

I have created a spreadsheet where I enter the date and time into cell A3 (formatted as dd-mmm-yy, hh:mm). In cell A4, i have created a formula that uses a vlookup to search "table1" for the local time zone conversion factor. My formula reads "=A3-VLOOKUP(A1,Table1,4,FALSE). My formula works great, as long as you subtract the vlookup value fm A3. As I travel across timezones, eventually I will need to add time to A3.

How can I write this formula to return the local time, weather it is GMT -7:00 or GMT +4:00, without going in and actually changing the formula to read "=A3+VLOOKUP(A1,Table1,4,FALSE)? I need it to search the table to find out weather it should add 7 hrs, or subtract 8 hrs (depending on the timezone I am in). Is there a better way to create table1 so that when it looks it up, it will "just know" to add/subtract x hrs fm the time entered?

Currently I am still a little unsure about which time format to use when entering the station time (i.e. New York GMT -4:00; do I use hh:mm, [h]:mm, general, etc?), but I want the dispayed results to be displayed juat as I entered the original time (dd-mmm-yy, hh:mm).

ANY help to get me headed in the right direction would be great. Thanks again for the posts!

Mark


I am TRYING to write a formula that incorporates "VLOOKUP" and "RIGHT" - and can't seem to get it right. Below is what I have:

(VLOOKUP (c2,TABLE1,(RIGHT(5,4)),FALSE)

Where I am trying to pull the 5th column from a table defined, and get only the last 4 digits.

Can anyone help?

THANKS


The fomula is a mix of VLOOKUP and SEARCH:

=VSEARCH(A1;table1!A:A;2;false)

In the A1 is the Text “Shell GmbH”
In the A2 is the Text “Deutsche Shell”
In the A3 is the Text “Shell LTD.”

In Table1 Colum A it says Shell
In Table1 Colum B it says Shell Group

The result would be that the formula would give back always “Shell Group”.

The option “true” would even accept “Shel” or “Schell”

Thanks,
Eckhard




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.


I have this query where I have two items("t51" or "041") in a field to not equal plus equal another field "type" to equal "pr", but it pulls all data instead of excluding. What am I doing wrong? below is my SQL


SELECT Table1.case, Table1.file, Sum(Table1.amount) AS SumOfamount
FROM Table1
GROUP BY Table1.case, Table1.file
HAVING (((Table1.case)"041") AND ((Table1.file)="pr")) OR (((Table1.case)"t51") AND ((Table1.file)="pr"));