If Or/and Statement

                      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)

                      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)

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"));