December 8, 2017

Hello friend;

Good Morning

**Worksheet name : case sensitive** I need some help in mastering performing a case-sensitive lookup , am getting wrong result for 2 array formulas, after entering array formula both times, hold Ctrl, shift, then, hit enter at same time.

**Worksheet name**** :**** multiple tables** Also, like to master how to write 2 or 3 nested IF formulas inside 1 Vlookup for Sales Rep - Alicia - Eric.

Thank you very much.

Have a nice day.

VIP

Trusted Members

June 25, 2016

Hi Mitul

**CASE-SENSITIVE LOOKUP**

If you look at your ARRAY formula in cell B10 =INDEX(NUMBER,MATCH(TRUE,EXACT(**Value**,NAME),0)) what is **Value** referring to?

The actual formula should be =INDEX(NUMBER,MATCH(TRUE,EXACT(**B9**,NAME),0))

As for your other LOOKUP formulas, you are "lucky" that they gave the correct answer due to the sorting sequence of the lookup range.

Try changing cell B6 to **DOG** (instead of **dog**) and it will not give you the correct answer (300 instead of 700).

**MULTIPLE TABLES**

I suggest you rename your tables properly so that they can be easily referred to. Example Table1, Table2, etc

Then in cell D2 you can enter **=VLOOKUP(C2,IF(B2<3,Table1,IF(B2<=6,Table2,IF(B2<=9,Table3,Table4))),2,TRUE)**

Hope this helps.

Sunny

Answers Post

December 8, 2017

Hi Clark;

Good Morning

I tried below formula in Multiple tables , It worked perfectly. I appreciate your help. Thank you very much.

I am still learning both OFFSET and LOOKUP formulas, Can you Please explain me - In worksheet attached below, ** <3 Years Tenure Table** range

is G1 : H9, but in formula it says , OFFSET(G$1 :H$12), still why it worked perfectly? I like to learn in depth, how OFFSET formula works and also like

to learn in depth how LOOKUP formula works. Please explain me below formula.

Thank you very much.

Have a great day

**MULTIPLE TABLES**

D2=VLOOKUP(C2,OFFSET(G$1:H$12,,LOOKUP(B2,{0,0;3,3;7,6;11,9})),2)

Please have a try.

December 8, 2017

SunnyKow said

Hi Mitul

CASE-SENSITIVE LOOKUPIf you look at your ARRAY formula in cell B10 =INDEX(NUMBER,MATCH(TRUE,EXACT(

Value,NAME),0)) what isValuereferring to?The actual formula should be =INDEX(NUMBER,MATCH(TRUE,EXACT(

B9,NAME),0))As for your other LOOKUP formulas, you are "lucky" that they gave the correct answer due to the sorting sequence of the lookup range.

Try changing cell B6 to

DOG(instead ofdog) and it will not give you the correct answer (300 instead of 700).

MULTIPLE TABLESI suggest you rename your tables properly so that they can be easily referred to. Example Table1, Table2, etc

Then in cell D2 you can enter

=VLOOKUP(C2,IF(B2<3,Table1,IF(B2<=6,Table2,IF(B2<=9,Table3,Table4))),2,TRUE)Hope this helps.

Sunny

November 19, 2017

Hi Mitul,

The first table [

I will show you a simple example of Lookup function.

Lookup value is 6 in cell K14, the table range is J18:K21. The formula =LOOKUP(K14,J18:K21) works as follows:

Lookup function lookup the value of K14(6) in the first column of the table J18:K21, the largest number in the first column which is smaller or equal to 6 is 3, and 3 is the second number in the first column. Lookup function returns the second value of the second column, result is 3.

=LOOKUP(10,J18:K21) will retun 6, because in the first column, the larges number which is smaller or equal to 10 is 7 which is the third number in first column, so Lookup finally returns the third number in second folumn 6.

you can try more examples.

=LOOKUP(11,J18:K21)=9

=LOOKUP(13,J18:K21)=9

November 19, 2017

Offset is more complicated.

OFFSET(reference, rows, cols, [height], [width])

I will show you some examples.

=OFFSET(A1,0,0) refers to cell A1, it means set cell A1 as base, offset 0 row down and offset 0 column to the right.

=OFFSET(A1,3,4) refers to cell E4, it means set cell A1 as base, offset 3 row down and offset 4 columns to the right.

=OFFSET(A1:B5,0,4) refers to range E1:F5, it means set A1:B5 as base, offset 0 row down and offset 4 columns to the right.

VIP

Trusted Members

December 7, 2016

Hello,

There is a big treasure hidden in this site which we refer to as MOTH, that is all the rich and excellent explanations of Excels functions and you find them in the blog section.

More about OFFSET --> https://www.myonlinetraininghu.....-explained

Br,

Anders

1 Guest(s)