Lookup value based on IF

**asabti** · 11-07-2008, 10:43 AM

Good morning.. here is my delema...

cat ---- dog ---- apple ---- orange
1 ------ 4 ------ 7 ------- 10
3 ------ 6 ------ 9 ------- 12

The above is a simplified example of waht i'm trying to do...

basically i have a value say (5) I want to check if its in the range of the numbers and return the value in a certain row...

So basically If the value is bigger than whats in the second row and smaller than whats in the third row return the corrosponding value in the first row

in this case for (5) it would be dog... i tried doing this with multiple if statements but i have about 15 columns of info and it tells me that the formula is too big...

Thanks in advance!

**daddylonglegs** · 11-07-2008, 10:47 AM

If your data is in A1:D3 and lookup value [5] in E2 use this formula in F2

=LOOKUP(E2,A2:D2,A1:D1)

As you can see row 3 isn't used, it's not necessary if your ranges are contiguous

**asabti** · 11-07-2008, 11:48 AM

thats great! it worked to solve the problem but i should elborate on the actaul application becuase it is a little more advanced than that!

-------Dog-------Cat--------Apple
-------1----------3-----------5
-------3----------5-----------7
-------MIX1------MIX1-------MIX1
X-------965--------580--------658
Y-------568--------432--------745
Z-------325--------486--------423
-------MIX2------MIX2-------MIX2
X-------220--------256---------856
Y-------623--------246---------875
Z-------260--------456---------623
-------MIX3------MIX3-------MIX3
X-------658-------756--------985
Y-------123-------732--------432
Z-------638-------754--------231

So i have 3 refrences... lets say 6 and mix 2 and Y

So i need 875 returned...

**daddylonglegs** · 11-07-2008, 12:09 PM

That probably isn't the best layout to achieve what you want but you can do that as per the attached. Note that X, Y and Z must be in the same order on each section

**ChemistB** · 11-07-2008, 12:21 PM

I did it similarly to the way DLL did it but I set up defined names for each table (Mix1, Mix2, Mix3) and with the indirect function, pointed the INDEX function in the right direction. With your variables in B18,19 and 20, the formula becomes

'=INDEX(INDIRECT(B19),MATCH(B20,A5:A7,0),MATCH(B18,B2:D2,1))

This assumes that the variable down column A (X,Y,Z) is the same for all mixes. See Attached.

**asabti** · 11-07-2008, 04:15 PM

Thanks the file you sent was really helpfull... I want to add another refrence now... i've attached an excel file... solving this should take care of the entire problem!

Thanks in advance