You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- EXCEL VLOOKUP AND INDEX

Advertisement

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

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

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:

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

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.