If and vlook up together

[Ryan Ostorero]

I'm working on spread sheet and I had never used the IF and VLOOKUP functions together. I experimented with it on a blank sheet. I got it figured out on my test sheet and it was working fine but when I go to use it on the sheet i actually need it on i get #VALUE! error. I'm not understanding where I screwed up?!?!

One that works
=IF(A12=$J12:$J18,$K$11:$K$17,VLOOKUP(A12,$J$11:$K$17,2,0))*B12+(C12*27.35)


One that gives me the #VALUE! error
=IF(K4=$X$250:$X$256,$Y$250:$Y$256,VLOOKUP(K4,$X$250:$Y$256,2,0))*T4+(H4*27.35)

To me the only difference is the location of the cells that are getting selected. Am I wrong?

 

[candybg]

What jumps out at me right off is that ranges *typically* are consistent within formulas with respect to ROWS. In your first formula, you have two row ranges: 11-17 and 12-18), but in the second formula, all rows have the same range 250-256.

 

[Ryan Ostorero]

thanks for catching that. I fixed it and still get the same error.

 

[Ryan Ostorero]

Basically the $J12:$J18,$K$11:$K$17 is two columns that are in a table that A12 is looking at to see if anything matches and if it does then there is a value in $K$11:$K$17 that then needs to be multiplied by a different number which is found with the VLOOK UP formula.

It's giving me a headache why it won't work.

 

[Khalid NGO]

Hi Ryan,

The red part of your formula seems array:
=IF(A12=$J12:$J18,$K$11:$K$17,VLOOKUP(A12,$J$11:$K$17,2,0))*B12+(C12*27.35)

I have not understood the logic though, have you tried entering with Ctrl+Shift+Enter?

Regards,

 

[Ryan Ostorero]

I fixed that already. I'm not sure why but when i have my table that the VLOOKUP is looking at to the right of my data it calculates fine verses having it below the data or on another sheet. For now I will call it good but it's not exactly what I wanted. No biggie.