Vlookup

[MattFD]

Hi, is it possible to use a variable Index value in a VLOOKUP formula based on the value of a cell? Something like this: =VLOOKUP(10251, A1:B6, X1, FALSE) where cell X1 is defined by a formula like: =if(A1="true"(B1="false"(C1="4x6", then "x")

Thanks, I'm pretty much a novice here, but am looking to create a quote sheet for mailing pieces that have some variable data to account for.

Thanks!

[Sam Capricci]

you can use a vlookup where you have another formula in X1 but the value location in X1 is where in a vlookup formula that you are telling the formula how far to go. So if you are referencing A1:B6 you would typically see either a 2 in X1 or a 2 where X1 is in your formula. If you have true or false or 4x6 in X1 that will not tell the formula how far over to go to find the value in the lookup referenced A1:B6. Does that make sense?

[TMS]

In your VLOOKUP, you are searching for 10251 in column A (A1:A6) and returning a matching value. In this instance, the matching value can only really be in column B ... though you could return the search value from column A. X1 would determine which column to return from the matrix so, in this instance, I don't think it will do what you want.

[MattFD]

Thanks for replying! Okay, so it appears that the Index value is hard coded into the formula and can't be a variable.

[MattFD]

Thanks for replying! I think it makes sense. I should have looked at my example better. The table reference A1:B6 in my example is arbitrary. In my actual reference table, I have 12 columns, so my X1 cell value would be 1-12 depending on the 3 variables in A1, B1, C1.

So, my hope was that the Index value in the VLOOKUP formlula, could be based on the value represented in cell X1.

[Sam Capricci]

I'm not sure I understand your comment in post #5. So going by what you wrote in post #4 if you have 12 columns of data and they begin in column A that means they go over to column L. If in cell X1 you have an if formula that returns either what is in A1 or B1 or C1 depending on values, say what it returns from A1 is a 4, from B1 it would be a 6 or from C1 it would be a 12, then if your vlookup formula is this =VLOOKUP(10251,A1:L6000,X1,FALSE) and your value in X1 is 4 when the formula finds 10251 in column A between rows 1 and 6000, it will go over to the 4th column (column D in this instance) and return the value there. If it is a 6 it'll return the value in column F, etc.

[TMS]

Yes, it can be a variable but, logically, it would need to return a value between 2 and 12. Your IF example (which doesn't work syntactically) would return a text value of 4x6 or x. Neither of those values would work as a (numeric) column Index.

[MattFD]

Thank you guys! Sorry for my struggle articulating my questions.

Awesome, TMS--how do I format the Index value within the vlookup formula to accomplish that?

[TMS]

Without using X1:

=VLOOKUP(10251, A1:L1000, IF(AND(A1="true", B1="false", C1="4x6"), D1*E1, F1*G1), FALSE)

Which makes interesting reading as you are looking for a numeric value in column A but then A1 is shown as potentially = "true".

Getting confused now ...

[TMS]

where cell X1 is defined by a formula like: =if(A1="true"(B1="false"(C1="4x6", then "x")

Maybe:

IF(AND(A1="true", B1="false", C1="4x6"), 2, 3)

would return a value of 2 if A1="true" and B1="false" and C1="4x6", otherwise it would return 3.

Or, to make it dependent on other cells on the row, for example,

IF(AND(A1="true", B1="false", C1="4x6"), D1*E1, F1*G1)

[Sam Capricci]

If you have something like this in X1

=if(A1="true"(B1="false"(C1="4x6", then "x")

that will cause problems within a vlookup formula. something like this =if(A1="true"(B1="false"(C1="4x6", then "x") looks like it should be closer to this for syntax purposes =if(AND(A1="true",B1="false",C1="4x6"),"x","") but this would still not return a value in X1 that would do anything for your vlookup. Instead this =if(A1="true",2,IF(B1="false",4,if(C1="4x6",8,""))) those values of 2 or 4 or 8 would tell the vlookup how far over to go to find the value to return for 10251. But without an actual worksheet attached it is difficult to correct formula issues.

[MattFD]

Thanks dudes! I'll work on the cell formula to get a 1-12 value, but what do I put here to reflect that cell value: =VLOOKUP(10251, A1:B6, ????, FALSE)

[Sam Capricci]

=VLOOKUP(10251, A1:B6, ????, FALSE)

probably 2 because based on this
A1:B6 the farthest it can go to the right is over to column B to find the value.

[Sam Capricci]

So going by what you wrote in post #4 if you have 12 columns of data and they begin in column A that means they go over to column L. If in cell X1 you have an if formula that returns either what is in A1 or B1 or C1 depending on values, say what it returns from A1 is a 4, from B1 it would be a 6 or from C1 it would be a 12, then if your vlookup formula is this =VLOOKUP(10251,A1:L6000,X1,FALSE) and your value in X1 is 4 when the formula finds 10251 in column A between rows 1 and 6000, it will go over to the 4th column (column D in this instance) and return the value there. If it is a 6 it'll return the value in column F, etc.

from my post #6. If you want something in X1 then I gave you an example of what could be there if it is an IF / THEN statement in post #11

Instead this =if(A1="true",2,IF(B1="false",4,if(C1="4x6",8,""))) those values of 2 or 4 or 8 would tell the vlookup how far over to go to find the value to return for 10251.

So you can either hard code a number in the vlookup or use a formula that will give you a result based on the contents of another cell.

[MattFD]

Hardcoding the Index value won't work, because it needs to be variable. What do I put here: =VLOOKUP(10251, A1:B6, ????, FALSE) so that it pulls the content of another cell? Not the result of the formula, but in the formula itself. So if X1 is "4" (X1 being a cell outside of the reference table) what do I put in place of the ???? above so it's "4"?

[Sam Capricci]

see my attached example for how you can use X1 - what is in X1 depends on how you write the if / then formula.

[MattFD]

Great, so all I need to do is put the cell in the vlookup formula. Thank you.

[Sam Capricci]

if that solves your problem then don't forget to mark the post as solved using the thread tools dropdown at the top of the post. And since you're new to the forum you can thank ALL those who've stopped by and tried to help you by clicking the "* Add Reputation" at the bottom of one of their posts, that is how we advance on this forum.

[TMS]

You're welcome. Thanks for the rep.