If and Lookup

[fizzwolf]

Hello I'm new on here today. Sorry if this has been covered elsewhere too.

I have a spreadsheet where I want to, I think, use the IF and Vlookup function.

I have 5 different grades of pay A to E. If I enter any one of these grades in a specific cell I need a formula to read the grade then lookup the appropriate column in a table for that grade. Column A gives the Performance as a percentage 1-120 then column B, C, D, E, and F have the Grade A to E. Hence if I type A as the Grade I want the lookup to give the answer in column B of the table and so forth.

I need E40 to look at G2 and lookup the value in the table for G40 (the percentage)

[kishoremcp]

Attach a sample file to help you

[Sam Capricci]

Agree w/kishoremcp that a sample might be very helpful.
That said, it almost sounds like you might need an HLOOKUP insteas of VLOOKUP.

[Fotis1991]

So in G40 you need a result of 0 ???

[ramananhrm]

Please try this file.

[fizzwolf]

So in G40 you need a result of 0 ???

G40 will have a number in it (percentage) which is listed on the Pay Lookup Table with the appropriate amount next to it. I need D40 to lookup G40 and give the value for it in the table but read the correct column (INDEX) in the table for the Grade A,B,C,D or E as specified in cell G2.

Hence an employee' work is timed and whatever percentage (cell G40) they have worked at over the week will give the hourly rate. Each employee then has a variable Grade so e.g. Grade A at 100% would be paid 8.93 per hour whereas if the employee is Grade C they would get 7.755 per hour. I need a formula that firstly looks at the Grade given in cell G2 then reads the corresponding column in the Pay Lookup Table for that Grade and the percentage given (cell G40).

I'm truly sorry for the confusion

[fizzwolf]

Please try this file.

Thank you very much. That is perfect.

[Fotis1991]

In fact you don't need the red part of the formula.

=+INDEX(INDEX('Pay lookup table'!$B$2:$F$101,0,MATCH($G$2,'Pay lookup table'!$B$1:$F$1,0)),MATCH(G40,'Pay lookup table'!$A$2:$A$101,0))

You can use the formula in this way.

=INDEX('Pay lookup table'!$B$2:$F$101,MATCH(G40,'Pay lookup table'!$A$2:$A$101,0),MATCH(G2,'Pay lookup table'!$B$1:$F$1,0))

[:) Sixthsense :)]

Or

=IFERROR(VLOOKUP(G40,'Pay lookup table'!$A$2:$F$101,MATCH(G2,'Pay lookup table'!$A$1:$F$1,0)),"")