You are here:

Excel/EXCEL VLOOKUP AND INDEX

Advertisement


Question
QUESTION: Hello
I am looking to construct a formula for the following table.

LOOK FOR 2000 THAN MATCH 098 THAN RETURN VALUE IN COLUMN B
LOOK FOR 2000 THAN MATCH 099 THAN RETURN VALUE IN COLUMN B

A   B   C   D
2000   1.0000   1.2288   098
2000   2.0000   0.9329   099
2000   3.0000   5.4456   100
2000   4.0000   0.1302   102
2000   5.0000   0.1181   103
2000   6.0000   0.0000   200
2000   7.0000   0.0000   400
2000   8.0000   0.7520   610
1000   5.0000   0.0000   098
1000   12.3000   0.0000   099
1000   8.0000   0.0000   100
1000   12.3000   0.0000   102

ANSWER: SHAH

since you are returning a number and it is from a unique row (combination of value in column A and D are unique) then

=Sumifs(B:B,A:A,2000,D:D,"098")  in excel 2007 or later version

in any version

=Sumproduct(--($A$1:$A$1000=2000),--($D$1:$D$1000="098"),$B$1:$B$1000)

in either formula, the hard coded values 2000 and "098" can be replaced with a reference to a cell that contains that value.

--
Regards,
Tom Ogilvy




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

QUESTION: Hi Tom
thanks for your quick reply, actually i think i did not ask question properly.


i am working on a spread sheet which have Apples, Oranges and Peaches and so on. I want to seperate their relative cost (column B & C) but by column D.

So formula may be look for Apples and than look for 098 if both matches than take the value from either column B or C. If Apples and 098 does not match than look for the second match....than look for 3rd and so on.

Below formula only check the Apples at first row but does not check the rest.

=IF(VLOOKUP(A1,sheet1!A:D,4,0)="098",VLOOKUP(A1,sheet1!A:D,2,0))
it does not work just giving you an idea.

A   B   C   D
Apples   1.0000   1.2288   098
Apples   2.0000   0.9329   099
Apples   3.0000   5.4456   100
Apples   4.0000   0.1302   102
Apples   5.0000   0.1181   103
Apples   6.0000   0.0000   200
Apples   7.0000   0.0000   400
Apples   8.0000   0.7520   610

Answer
formula
formula  
Shah,

I gave you an answer that I feel will do what you want.  If you are not willing to use it, then I don't know what you expect me to say.  The formula you show is a dead end.  It will not do what you want unless you want to concatenate information from columns A and D in another column and do your look up on the concatenated results.

You added information about B and C - I assume you want to look up each of B and C separately, so you could use my formula twice to do that.

I have attached an image demonstrating the formula I provided actually returning the proper values as I understand you requirement.  Hopefully that will give you some confidence that the formula suggested will work.  (I did use cell references for the desired values rather than hard coding them).

--
Regards,
Tom Ogilvy

About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.