Excel/EXCEL

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: I'm not clear what you want to return here
LOOK FOR 2000 THAN MATCH 098 THAN RETURN VALUE IN COLUMN B
doesn't make it clear what I am searching for (possibly the use of THAN is confusing me?) - is it simply that you want to match two values and return a third?  If so, this is probably the simplest method

=SUMPRODUCT(--($A$1:$A$12=2000),--($D$1:$D$12=98),$B$1:$B$12)

in this instance, rows 1 to 12 contain the data in your table, and I'm matching actual values of 2000 and 98 rather than cell references - in practice, I would expect to alter the 2000 and 98 to cells which contain these values.  This formula would return a zero in the event of no exact match - I'm not sure if that scenario needs to be handled seperately or if it isn't a possibility?  NOTE that this formula is a normal formula, but the double negative signs are important for it to work properly.

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

QUESTION: Hi
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
It just got a lot more complicated!  This time we need an array formula

=INDEX($B$1:$B$9,MIN(IF($A$1:$A$9=F2,IF($D$1:$D$9>=F3,ROW($A$1:$A$9),9999))),1)

where b1:D9 relate to your example data and F2 contains Apples, F3 contains the number you want to match - enter the formula by pressing ctrl shift enter. In this example if we were looking up Apples 101 it would return the value for apples 102 - amend index(b1:b9 to whichever column you want to return the data from.
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


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.