Combining IF with different Vlookup arrays

[Jjagz]

I have looked at other suggestions for using vlookup within an if statement. I have tried the following formula without success. C3 is a drop down list which determines which array I want to use.

=IF($C$3=“Standard”,VLOOKUP(A7,Inputs!$H$5:$J$47,3,FALSE),vlookup(a7,Inputs!$L$5:$N$47,3 FALSE))

Any ideas what is incorrect with this formula?

Thanks,

Jjagz

 

[FDibbins]

If you give each range a name that matches what you have in the DD, you could then just use a single vlookup

Alternatively, maybe use INDEX/MATCH/MATCH instead, but for that I would need to see a sample of what you have, and what you want?

 

[Jjagz]

I don't know how to give each range a name so I was trying to work within what I do know

C3 refers to the names of two rate tables which do correspond to the vlookup arrays..
A7 is also a drop down list for our employees.

Depending on which rate table is selected I want it to look up the correct billing rate for A7's value

Thanks,

 

[Aladin Akyurek]

Is this what you are after?

=VLOOKUP(A7,INDIRECT(C3),3,0)

 

[Jjagz]

Is this what you are after?

=VLOOKUP(A7,INDIRECT(C3),3,0)

Thank you for your response. - I'm not sure what the Indirect or the /Quote does.

Strangely I kept playing with my formula and if I use the same table array, only reference a different column for the returned value the formula works!

=IF(C3="Folsom",VLOOKUP(A7,Inputs!$H$5:$J$47,3,FALSE),VLOOKUP(A7,Inputs!$H$5:$J$47,4,FALSE))

 

[FDibbins]

What does your data look like? Can you show a sample please?

 

[Jjagz]

What does your data look like? Can you show a sample please?

I ended up using this formula which is only different in that I combined the new rate calculation in the same array:

=IF(C3="Folsom",VLOOKUP(A7,Inputs!$H$5:$J$47,3,FALSE),VLOOKUP(A7,Inputs!$H$5:$K$47,4,FALSE))

My Inputs table looks like this now:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Classification[/TD]
[TD]Name[/TD]
[TD]Folsom Rate[/TD]
[TD]Bay Rate[/TD]
[/TR]
[TR]
[TD]Survey Technician[/TD]
[TD]Smith[/TD]
[TD]$145.00[/TD]
[TD]$152.00
[/TD]
[/TR]
[TR]
[TD]Senior Surveyor[/TD]
[TD]Jones[/TD]
[TD]$155.00[/TD]
[TD]160.00[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1483"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]So if C7 = "Folsom" it should return the value based on A7 (in this case Survey Technician) which is $145.00 else it will return $152.00

Originally I had two completely different rate tables with 3 columns each on the Inputs sheet but referencing the two arrays in the formula as originally posted did not work. Using a 4th column for the Bay rates within the same table range worked. I don't understand why the first one failed but I'm glad I got it to work.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks for your response!

JJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Originally I had two completely different rate tables with 3 columns each on the Inputs sheet but referencing the two arrays in the formula as originally posted did not work.

The only problems with your original formula in post #1 were
1. You had 'sloping' quotes where you needed 'vertical' quotes, and
2. You were missing the comma before the final FALSE

=IF($C$3="Standard",VLOOKUP(A7,Inputs!$H$5:$J$47,3,FALSE),VLOOKUP(A7,Inputs!$L$5:$N$47,3,FALSE))

I have looked at other suggestions for using vlookup within an if statement. I have tried the following formula without success. C3 is a drop down list which determines which array I want to use.

=IF($C$3=“Standard”,VLOOKUP(A7,Inputs!$H$5:$J$47,3,FALSE),vlookup(a7,Inputs!$L$5:$N$47,3 FALSE))

 

[Jjagz]

The only problems with your original formula in post #1 were
1. You had 'sloping' quotes where you needed 'vertical' quotes, and
2. You were missing the comma before the final FALSE

=IF($C$3="Standard",VLOOKUP(A7,Inputs!$H$5:$J$47,3,FALSE),VLOOKUP(A7,Inputs!$L$5:$N$47,3,FALSE))

Thank you for helping me see why the first formula failed! I didn't think I changed the quotes at all in my second formula, but I do see that I completely missed the comma prior the False.

I so reciate you taking the time to sharpen my vision on my formula building! I haven't tried to combine functions before

Thanks!
Jacquie

 

[Peter_SSs]

I didn't think I changed the quotes at all in my second formula ..

In your Excel sheet, your quotes may have been correct. If you copied your formula from Excel and pasted in, say, Word to compose the rest of your forum question, the quotes may have been altered by Word. Anyway, the main this it was resolved.

Thank you for helping me see why the first formula failed!

I so reciate you taking the time to sharpen my vision on my formula building! I haven't tried to combine functions before

Thanks!
Jacquie

You are most welcome.