0 
          A           B              C                 D
1   Distance 1  Area 1  Distance 2  Area 2
2      100        11       200        16
3      300        12       400        17
4      500        13       600        18
5      700        14       800        19
6      900        15       1000       20

I need formula to return area in B9 after entering distance value in A9. If I enter 300 in cell A9 then B9 should return 13. If I enter 700 in A9, then B9 should return 17. I know if distance and areas be in single column then INDEX MATCH formula can be used. But I can't set values in single columns. Please help which formula shall work? Isn't there any formula without using IF? Like INDEX(Column A & Column C(Match A9, In Column B & Column D))

Improve this question
1
  • Can you explain the relationship between input and output? Why does 300 >> 13 and 700 >> 17? It's not at all clear to me in this question - and I think the answers below were written before you edited your table and moved everything around.
    – Alex M
    Commented Mar 9, 2020 at 17:26

2 Answers 2

Reset to default
1

In B9, enter formula :

=SUMIF(A2:C6,A9,B2:D6)

enter image description here

Improve this answer
1
  • thanks dear. You made my day. I was thinking only of INDEX MATCH, but didn't think that SUMIF will work well as the DISTANCE columns have unique values.
    – Renu
    Commented Mar 10, 2020 at 2:15
2

In B9 enter:

=IF(A9<=500,VLOOKUP(A9,A2:B6,2),VLOOKUP(A9,C2:D6,2))

enter image description here

(MATCH() and INDEX() can be used in a similar fashion)

Improve this answer
2
  • The only change I would make is the =IF(A9<600,... as 501 to 599 will cause an error.
    – Scott Craner
    Commented Mar 9, 2020 at 15:29
  • @ScottCraner You are correct I assumed that the input in A9 would be an exact match to one of the table values.
    – Gary's Student
    Commented Mar 9, 2020 at 15:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .