You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Excel vlookup a formula

Advertisement

QUESTION: Good day to you Sir its been a long time since I asked a question.

My question is about a vlookup/lookup procedure.

I have a cell with a formula with sits in column E5: =sumif(a:a,8210,b:b)+sumif(a:a,8250,b:b)+sumif(a:a:,8220,b:b)+sumif(a:a,8215,b:b)

Actually its longer, the formula sums all numbers in column A and its amounts in column B and places it in column E5. But the problem arises when there is a new number such as 8300, 8301, 8302 added in column A but are not part of the sumif formula in column E. Given there are like 150 new numbers it would be hard for me to find the new ones from those that are already inside the sumif formula.

I hope you could help me find a formula to find the new numbers and look inside a formula. All the google searches I made are vlookup for cell value and not the formula itself. I just want to find out which are the new ones that are not included in the sumif formula given its numerous numbers.

Thank you and God bless.

Ariel

ANSWER: Maybe I'm misunderstanding, but it seems like if you want all the numbers found in A to be included, your correct formula would simply be =SUM(B:B)

!!

---------- FOLLOW-UP ----------

QUESTION: good day thanks for the reply. Sorry for the unclear question but like i said there are more formulas.

In column A all the numbers like 8215, 8250, 8345, 8500, 8600, 8700 are present there are times that new numbers come in like 8701, 8730. To be clear this are telephone numbers.

The formula in column E2 is = Sumif(a:a, 8215, b:b)+sumif(a:a,8250,b:b)+sumif(a:a,8345,b:b) we call it 1st floor

and next formula is on E3 = sumif(a:a,8500,b:b)+sumif(a:a,8600,b:b)+sumif(a:a,8700,b:b) thats the 2nd floor.

But the problem arises if new numbers like i said if 8701 & 8730 which is not part of the 2 formulas. What is 8701 is on the 1st floor and 8730 is on the 2nd floor.

This may sound simple but what if there are 10 floors with 10 formulas and different numbers.

I need to need that in column A which numbers are not included in the formulas in column E. i tried using CTRL+F but given the numerous numbers i will waste 1 day to find it all.

Thank you and God bless.

You could list all the values you have in the formulas, like

8215

8250

8345

8500, etc.

Say that is in C1:C50 and your data is in A1:A1000.

Enter this in E1 via ctrl+shift+enter and fill down. It will display the items in C not in A:

=IFERROR(SMALL(IF(ISNA(MATCH($C$1:$C$50,TRANSPOSE($A$1:$A$1000),0)),$C$1:$C$50,""),ROW(A1)),"")

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."**Publications**

Excellence, The Expert, Microsoft**Education/Credentials**

BA in math, Hofstra University, 1965**Awards and Honors**

MVP

Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks