Broken Lookup Function?

[tyija1995]

Hi all,

I've recently encountered some strange behaviour using Excels LOOKUP function, and I can't figure out why it is doing this.

For now I have used an INDEX MATCH to get around this but I am still baffled, please see below:

Server_DiskSpace_Usage_27032020.xlsx
	A	B	C	D	E	F	G
1	Worksheet	Row Count (Column A)	Person	Broken??		Server	Person
2	Ares_G_27032020	212	Matt	Matt		Ares	Matt
3	Artemis_G_27032020	83	Nicole	Nicole		Artemis	Nicole
4	Awacdev_G_27032020	11016	Henry	Nicole		Demeter	Sadie
5	Awacdev_H_27032020	847	Henry	Nicole		Hades	Batuhan
6	Demeter_G_27032020	118	Sadie	Sadie		Hephaestus	Tom
7	Demeter_H_27032020	61	Sadie	Sadie		Medusa	Mikaela
8	Hades_G_27032020	67	Batuhan	Batuhan		Zeus	James
9	Hades_H_27032020	638	Batuhan	Batuhan		Awacdev	Henry
10	Hephaestus_G_27032020	60783	Tom	Tom		Oracle	Henry
11	Medusa_G_27032020	55	Mikaela	Mikaela
12	Oracle_G_27032020	12055	Henry	Mikaela
13	Oracle_H_27032020	99652	Henry	Mikaela
14	Zeus_G_27032020	144	James	James
Row_Count_Analysis

Cell Formulas
Range	Formula
B2:B14	B2	=LOOKUP(2,1/(INDIRECT($A2&"!A:A")<>""),ROW(INDIRECT($A2&"!A:A")))
C2:C14	C2	=INDEX($F$2:$G$10,MATCH(LEFT(A2,FIND("_",A2)-1),$F$2:$F$10,0),2)
D2:D14	D2	=LOOKUP(LEFT(A2,FIND("_",A2)-1),$F$2:$F$10,$G$2:$G$10)

It is column D i am concerned with that is incorrect, column C is correct and B is just the row counts for each worksheet - any ideas on why col D is incorrect for some of the rows (highlighted) and not all??

 

[snjpverma]

For Lookup to work, you require to have the Column F sorted in ascending order. Once you sort it, the things will sort out itself as shown below.

Book1
	A	B	C	D	E	F	G
1	Worksheet	Row Count (Column A)	Person	Broken??		Server	Person
2	Ares_G_27032020		Matt	Matt		Ares	Matt
3	Artemis_G_27032020		Nicole	Nicole		Artemis	Nicole
4	Awacdev_G_27032020		Henry	Henry		Awacdev	Henry
5	Awacdev_H_27032020		Henry	Henry		Demeter	Sadie
6	Demeter_G_27032020		Sadie	Sadie		Hades	Batuhan
7	Demeter_H_27032020		Sadie	Sadie		Hephaestus	Tom
8	Hades_G_27032020		Batuhan	Batuhan		Medusa	Mikaela
9	Hades_H_27032020		Batuhan	Batuhan		Oracle	Henry
10	Hephaestus_G_27032020		Tom	Tom		Zeus	James
11	Medusa_G_27032020		Mikaela	Mikaela
12	Oracle_G_27032020		Henry	Henry
13	Oracle_H_27032020		Henry	Henry
14	Zeus_G_27032020		James	James
florida

Cell Formulas
Range	Formula
C2:C14	C2	=INDEX($F$2:$G$10,MATCH(LEFT(A2,FIND("_",A2)-1),$F$2:$F$10,0),2)
D2:D14	D2	=LOOKUP(LEFT(A2,FIND("_",A2)-1),$F$2:$F$10,$G$2:$G$10)

 

[tyija1995]

Fantastic thank you so much! I had it working before I added the last 2 names - I shall amend the file now

 

[Peter_SSs]

There is no need to alter the order if you don't want to as you can use VLOOKUP instead.

20 03 30.xlsm
	A	B	C	D	E	F	G
1	Worksheet	Row Count (Column A)	Person	Broken??		Server	Person
2	Ares_G_27032020	212	Matt	Matt		Ares	Matt
3	Artemis_G_27032020	83	Nicole	Nicole		Artemis	Nicole
4	Awacdev_G_27032020	11016	Henry	Henry		Demeter	Sadie
5	Awacdev_H_27032020	847	Henry	Henry		Hades	Batuhan
6	Demeter_G_27032020	118	Sadie	Sadie		Hephaestus	Tom
7	Demeter_H_27032020	61	Sadie	Sadie		Medusa	Mikaela
8	Hades_G_27032020	67	Batuhan	Batuhan		Zeus	James
9	Hades_H_27032020	638	Batuhan	Batuhan		Awacdev	Henry
10	Hephaestus_G_27032020	60783	Tom	Tom		Oracle	Henry
11	Medusa_G_27032020	55	Mikaela	Mikaela
12	Oracle_G_27032020	12055	Henry	Henry
13	Oracle_H_27032020	99652	Henry	Henry
14	Zeus_G_27032020	144	James	James
Lookup

Cell Formulas
Range	Formula
D2:D14	D2	=VLOOKUP(LEFT(A2,FIND("_",A2)-1),$F$2:$G$10,2,0)

 

[tyija1995]

Thanks for another alternative method Peter

 

[Peter_SSs]

Thanks for another alternative method Peter

You're welcome.