Vlookups IF statements etc

hetal247

New Member
Joined
Dec 21, 2011
Messages
15
Hi,

I have 2 tables:
1611158687117.png

1611158719772.png


What I am trying to do is an vlookup and if statement to return the result column in table 1. so if my lookup value is 132, the for client 3, I want it to look at table 2 and find the result where value 1 and value 2 is between 3.2 therefore the result would be 4.

Is there an easier way to do this? I am struggling and been searching for a solution on the internet without any luck. Hoping that somebody can help me with this.

Much Appreciated

Hetal
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
Assuming no duplicate / overlapping entries in table2
Excel Formula:
=SUMIFS(Result,Index,132,Value1,"<="&Rating,Value2,">="&Rating)
I've used the column headings from your screen captures as descriptions in the formulas to show what each part should refer to.
 

hetal247

New Member
Joined
Dec 21, 2011
Messages
15
Assuming no duplicate / overlapping entries in table2
Excel Formula:
=SUMIFS(Result,Index,132,Value1,"<="&Rating,Value2,">="&Rating)
I've used the column headings from your screen captures as descriptions in the formulas to show what each part should refer to.
Hi Jason,

Thank you for coming back to me quickly. I have tried this and unfortunately still does not work. It is possible to have duplicate values 1 and value 2 in the table but when looking at the index, there should only be a single entry.

Hope that makes sense

kind regards

Hetal
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
There can be duplicates in the value cells as long as each row is unique, from the example in your post there are no duplicate rows.

Once you wet the formula to match your sheet ranges it will work fine.
 

hetal247

New Member
Joined
Dec 21, 2011
Messages
15
There can be duplicates in the value cells as long as each row is unique, from the example in your post there are no duplicate rows.

Once you wet the formula to match your sheet ranges it will work fine.
That's brilliant. It works now. Thank you for you help

kind regards

Hetal
 

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top