• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

using index/match to find the intersection of 2 variables - not working

sunloverbc

New Member
Please see the upload file for example:

Formula I have used =
=IF(B1<0,0,IF(B2<0,0,INDEX(C7:H12,MATCH(B2,B7:B12,1),MATCH(B1,C6:H6,1))))


problem - should be returning 1.70 but it is returning 0
 

Attachments

  • chandoo help.xlsx
    14.4 KB · Views: 9
Check the value in B1. It displays 0.5%, which would put you in the 0% column. When 5 is entered in B1, the rate is displayed as $1.70
 
Hi:

You do not need 2 If condition for the formula , you can shorten the formula like below:

IF(OR(B1<0%,B2<0%),0,INDEX(C7:H12,MATCH(B2,B7:B12,1),MATCH(B1,C6:H6,1)))

Thanks
 
See Bosco's comment in #4. By changing the headers to text (ie using ">", "-", or "<" in the header) you stopped the Match function from operating. I'd recommend using 0, 5, 10, 15, 20, and 100 for the headers. The function works with that. If you must do the headers as you have them above, use a separate part of the spreadsheet to list the 0 to 100 values and reference that in your function. (See attached for example)
 

Attachments

  • chandoo help (1).xlsx
    14.6 KB · Views: 6
thank you for the assistance Mike86. I only put the greater than less than to show the ranges. My actual file does not have that. I will try your suggestions today and let you know if I was able to get it to work. My issue is the first column that shows 0. this column should actually be referenced only if the number is less than 0. a number between 0 and 5 should pick up the next column and so on.
 
Back
Top