tyija1995
Well-known Member
- Joined
- Feb 26, 2019
- Messages
- 781
- Office Version
- 365
- Platform
- Windows
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:
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??
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??