Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

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

View Answers     

Similar Excel Tutorials

How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
Introducing Logic into Formulas and Functions in Excel
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...

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
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se

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:


                      A                       B                      C                      D
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:

                      A                       B                      C                      D
                      A                       B                      C                      D
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

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


  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 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.


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:

=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!


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



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

I would like change the Named Range in vlookup formulas using something like indirect reference. My formula is:

=vlookup(b4, Table1,4)*b4-vlookup(b4,Table1,2)

the named range need to be changed dynamically. there are 14 tables. Table1,Table2,Table3...

Can anyone help?

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...

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

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


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.


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.



I am trying to apply a COUNTIF function to only visible cells in a selected range. Here's the regular COUNTIF formula that I'm working with.


Column B in the current sheet contains dates, which are also present in the header row in TABLE1. This countif formula does the following:

1) Uses INDEX(TABLE1,0,MATCH($B8,TABLE1[#Headers],0)) to define the range as the column in TABLE1 with a header that matches the date in $B8.
2) Uses VLOOKUP(18,$AF$7:$AG$28,2,FALSE) to define the search term through a VLOOKUP of a small table in the current sheet.

The formula works just fine. However, I'm unsure of how to get it to ignore hidden rows. I know that you can do this with simpler functions using SUBTOTAL, and I have a VBA solution that works but is extremely slow. Any ideas?

Thanks for your help.

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

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

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?


* 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

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

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?



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

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


Here is my attempt.


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

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.


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 )

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):

  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!



I'vd got a table with a column called key that contains a single letter and then a number of any length.


I want to get the maximum number that exists in the table column, ignoring the letter prefix.

I've tried array formulas;

{=MAX(RIGHT(Table1[@Key],LEN(Table1[@Key])-1))} returns 1
{=MAX(RIGHT(Table1[Key],LEN(Table1[Key])-1))} returns 0

and non array formulas

=MAX(RIGHT(Table1[@Key],LEN(Table1[@Key])-1)) returns 1
=MAX(RIGHT(Table1[Key],LEN(Table1[Key])-1)) returns 1

I want it to return 6, as this is the maximum...



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:


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

Can anyone help?


I have two Tables, Table1 and Table2, in a single Excel spreadsheet. Table1 is our master log, and Table2 contains only Provider Names, Contact Names, Phone Numbers, and Email Addresses. I need to compare column K from Table1 with column AT from Table2. Whenever Excel finds a perfect match, I need Excel to copy AW:AY to AB:AD. For each value in column AT, there will be several identical matches in column K.

I have search long hours for a solution, have tried to implement vlookup() and index(match()), but cannot figure either one of them. Any help would be greatly appreciated.

fyi, Table1 is almost 1500 rows long, while Table2 is not quite 80.

Is there a function that will repopulate this list automatically without the multiple entries? I've been using lookup functions and max to add reference numbers on the side but was wondering if there is a simpler way.

Summary Sheet

  I J K L 5 1 02.5151 1 02.5151 6 2 02.5160 2 02.5160 7 3 02.5166 3 02.5166 8 4 02.5221 4 02.5221 9 0 02.5221 5 02.5225 10 0 02.5221 6 02.5383 11 5 02.5225 7 02.5432
Spreadsheet Formulas Cell Formula L5 =IF(ISERROR(VLOOKUP(K5,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K5,$I$5:$J$30,2,FALSE)) I6 =IF(COUNTIF($J$5:J6,J6)=1,MAX($I$5:I5)+1,0) L6 =IF(ISERROR(VLOOKUP(K6,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K6,$I$5:$J$30,2,FALSE)) I7 =IF(COUNTIF($J$5:J7,J7)=1,MAX($I$5:I6)+1,0) L7 =IF(ISERROR(VLOOKUP(K7,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K7,$I$5:$J$30,2,FALSE)) I8 =IF(COUNTIF($J$5:J8,J8)=1,MAX($I$5:I7)+1,0) L8 =IF(ISERROR(VLOOKUP(K8,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K8,$I$5:$J$30,2,FALSE)) I9 =IF(COUNTIF($J$5:J9,J9)=1,MAX($I$5:I8)+1,0) L9 =IF(ISERROR(VLOOKUP(K9,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K9,$I$5:$J$30,2,FALSE)) I10 =IF(COUNTIF($J$5:J10,J10)=1,MAX($I$5:I9)+1,0) L10 =IF(ISERROR(VLOOKUP(K10,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K10,$I$5:$J$30,2,FALSE)) I11 =IF(COUNTIF($J$5:J11,J11)=1,MAX($I$5:I10)+1,0) L11 =IF(ISERROR(VLOOKUP(K11,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K11,$I$5:$J$30,2,FALSE))

Excel tables to the web >> Excel Jeanie HTML 4


I am trying to count the number of unique values in a range (four columns wide, called Name One through Name Four) based on some criteria (currently just the Year). The problem is that my values are in text format so I can't do this:


=SUM(IF(FREQUENCY(IF((Table1[[Name One]:[Name Four]]<>"")*(Table1[Year]=A13),Table1[[Name One]:[Name Four]],""), IF((Table1[[Name One]:[Name Four]]<>"")*(Table1[Year]=A13),Table1[[Name One]:[Name Four]],""))>0,1,0))

So this is what I am trying:


=SUM(IF(FREQUENCY(IF((LEN(Table1[[Name One]:[Name Four]])>0)*(Table1[Year]=A13),MATCH(Table1[[Name One]:[Name Four]],Table1[[Name One]:[Name Four]],0),""), IF((LEN(Table1[[Name One]:[Name Four]])>0)*(Table1[Year]=A13),MATCH(Table1[[Name One]:[Name Four]],Table1[[Name One]:[Name Four]],0),""))>0,1,0))

But this second formula doesn't work, seemingly because the range in the Match function is a range that has more than one column.

These formulas I have tried came from Microsoft's Excel Help. Any ideas on what formula I should try instead?

I attached a sample, but it's not very pretty.