Looking up a value based on 2 conditions with HLOOKUP

Question

I have a pivot table based on a large group of data. I want to filter this data into a simple table to generate a report. I tried HLookup but it fails as I change the settings of the pivot table. so I need to do it with something like;

=if team is EN & its on Afternoon shift then the afternoon shift number is x.

Here is the google sheet link for more illustration: https://docs.google.com/spreadsheets/d/1mk5xhn9Su26ptmRMLINcKxViP2SgkKrjiA9jfy5a9wM/edit?usp=sharing

Answer 1

You can use Hlookup like this:

=transpose( {{"Team";A2:A7}, arrayformula( hlookup( if( row( A1:A7)^0*COLUMN(B1:I1)^0=1,B1:I1,""),B1:I7,sequence(counta(A1:A7),1),false))})

explanation:

1. {"Team";A2:A7} with transpose will give header:

   Team    Afternoon   AL  MC  ML  Morning Night
   

2. row( A1:A7)^0*COLUMN(B1:I1)^0 with array formula will give

   1 1 1 1 1 1 1 1 
   1 1 1 1 1 1 1 1
   1 1 1 1 1 1 1 1
   1 1 1 1 1 1 1 1
   1 1 1 1 1 1 1 1
   1 1 1 1 1 1 1 1
   1 1 1 1 1 1 1 1
   

3. if( row(A1:A7)^0 *COLUMN(B1:I1)^0 = 1, B1:I1,"")

   EN  DE  FR  UK  GE  BR  EG  IL
   EN  DE  FR  UK  GE  BR  EG  IL
   EN  DE  FR  UK  GE  BR  EG  IL
   EN  DE  FR  UK  GE  BR  EG  IL
   EN  DE  FR  UK  GE  BR  EG  IL
   EN  DE  FR  UK  GE  BR  EG  IL
   EN  DE  FR  UK  GE  BR  EG  IL
   

4. hlookup( if( row(A1:A7)^0 *COLUMN(B1:I1)^0 = 1, B1:I1,"") , B1:I7, sequence(counta(A1:A7),1),false) with arrayformula will give:

   EN  DE  FR  UK  GE  BR  EG  IL
   9   5   8   5   5   3   2   2
   1   1   1                   
   2       1   2               
                           1   
   6   6   5   4   4   2   1   1
   15  7   6   5   4   3   2   1
   

5. All formula will give (after transpose):

   Team    Afternoon   AL  MC  ML  Morning Night
   EN      9           1   2       6       1500%
   DE      5           1           6       700%
   FR      8           1   1       5       600%
   UK      5               2       4       500%
   GE      5                       4       400%
   BR      3                       2       300%
   EG      2                   1   1       200%
   IL      2                       1       100%
   

But the easy way, just use transpose:

=transpose({"team",B1:I1 ;A2:I7})   

Answer 2

try:

=ARRAYFORMULA(IFNA(VLOOKUP(L2:L, TRANSPOSE(A1:I11), {2,3,4,5,6,7}, 0)))

---

all in one:

=ARRAYFORMULA({IFNA(VLOOKUP(L2:L9, TRANSPOSE(A1:I11), {2,3,4,5,6,7}, 0));
   QUERY(QUERY(IFNA(VLOOKUP(L2:L9, TRANSPOSE(A1:I11), {2,3,4,5,6,7}, 0)), 
 "select sum(Col1),sum(Col2),sum(Col3),sum(Col4),sum(Col5),sum(Col6)", 0), "offset 1", 0)})

Answer 3

You can use

=QUERY(ARRAYFORMULA(TRANSPOSE($A$1:$I$11)),"SELECT Col2 where Col1 = '"&$L2&"'",0)

what this does is transpose your data to get it in the format you need, you can then specify the column based on the index of your left hand column i.e Col2 = afternoon

if you just want it transposed without any sort of querying or filtering you can just do

=ARRAYFORMULA(TRANSPOSE(A1:I11))

which will give you