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.
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
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.
Clark
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.
SunnyKow said
Hi MitulCASE-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
Hello Sunny;
This is Mitul.
Good Morning;
Both formulas (Case Sensitive AND Multiple Tables worked perfectly)
I appreciate it. Thank you very much.
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
Hello Clark;
This is Mitul.
Good Morning;
Thank you for explanation of LOOKUP formula. Can you Please explain me "OFFSET" function?
Thank you. Have a nice day.
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.
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.myonlinetraininghub.com/excel-offset-function-explained
Br,
Anders
