+ Reply to Thread
Results 1 to 3 of 3

Search for value within matrix of MAX and MIN values

  1. #1
    Registered User
    Join Date
    11-07-2011
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    5

    Search for value within matrix of MAX and MIN values

    Dear Forum members

    I'm a new user of the excelforum and I would like to ask for your help with an excel-formula. I guess the easiest way to explain my problem is to work with an example (see TEST worksheet in attachment).

    I would like to search for a value (in column B) within column H and I. If (B_rowA > H_rowX) and (B_rowA < I_rowX) AND A_rowA=G_rowX then give value F_rowX.

    So in the case of B3=6957771 it should return 01g015090 (as 6956388 (=H1458) <6957771<6963140 (=I1458) AND A3 (place_1)=G1458 (place_1)).

    So in the case of B30=883152 it should return 15g01990 (as 883111 (=H11536) <883152< 889020 (=I11536) AND A30 (place_15)=G11536 (place_15)).

    I already made an attempt (FORMULA-column), but till now the formula didn't work well because I cannot incorporate the condition of A_rowA=G_rowX)

    Any help is welcome!

    Kind regards

    Wannes
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Search for value within matrix of MAX and MIN values

    Hello Wannes,

    Try this formula in D3 copied down

    =LOOKUP(2,1/(B3<I$3:I$18165)/(B3>H$3:H$18165)/(A3=G$3:G$18165),$F$3:$F$18165)
    Audere est facere

  3. #3
    Registered User
    Join Date
    11-07-2011
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Search for value within matrix of MAX and MIN values

    That's fast! Your formula is exactly what I want!

    Thank you very much.

    Wannes

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1