• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hlookup is manual - Any other way to get the data

kiran5

New Member
Hi everyone,

A silly doubt, but not getting an idea.

I have a file with list of day codes in D column and to their respective volumes for each day is mentioned in the next sheet called list.

Based on the filter I choose in E3, the respective values should pick in E8, E9, E10 etc till E27.
For example:
If I choose E3 as Monday then, my E8 to E27 should be as below based on List sheet: Hlookup is very manual to change the row index number. Any other way of doing this pls?
100
150
250
320
440
120
160
180
240
265
290
315
340
365
390
415
440
465
490
515
 

Attachments

  • Choose Day.xlsx
    10.8 KB · Views: 6
Hi,

How about the below formula

=SUMPRODUCT((List!$A$2:$A$10=$D8)*(List!$B$1:$F$1=Data!$E$3)*(List!$B$2:$F$10))
 
You can go with the below function.

=IFERROR(INDEX(List!$A$1:$F$31,MATCH(Data!$D8,List!$A$1:$A$31,0),MATCH(Data!$E$3,List!$A$1:$H$1,0)),"")

Index used for creating array/range - List!$A$1:$F$31,
Match used for getting row number - MATCH(Data!$D8,List!$A$1:$A$31,0)
Again Match column number of array table - MATCH(Data!$E$3,List!$A$1:$H$1,0))
Iferror use for change #N/A into blank - ""
 
wow! thanks folks.
I thought sumproduct works for only to check multiple columns. Now, I understood.
Thanks Bhawani for your answer too and it was a nice explanation about the formula you mentioned which will just not a copy and paste for me even it is knowing what I am copying out of it. Thanks again.!
 
...Hlookup is very manual to change the row index number...

No Kiran, based on your sample file, you can use HLLOKUP with help of MATCH.

=HLOOKUP(E$3,List!B$1:F$10,MATCH(D8,List!A$1:A$10,0),0)

Also, in List tab if S.No are sequence numbers, you can simply reference Day in HLOOKUP row_index,

=HLOOKUP(E$3,List!B$1:F$10,D8+1,0)
 
Back
Top