You are here:

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

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.

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | No Comment |

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

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!