• 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.

vlookup for dynamic columns based on multiple data selections

X

xljgd

Member
Hello All,


I have 2 value that i select using a pull down. The number and the month.

At each row if i select say

no 1 and month May i should lookup the corresponding value.


I have used vlookup to get values from the table for all other columns

but not able to solve the value for the month column because it is based on

2 multiselect option No and Month.


Eg.

[pre]
Code: 
No	Jan	Feb	Mar	April
1	test	prepare	inspect	ship
2	ship	test	test	inspect
3	prepare	test	prepare	ship
if the above is my table.

[pre][code]Select a month  The month and no are from a drop down list.
No		Jan
1		test
3		prepare
2		ship
[/pre]
Next time when i change the month to Mar I should get

Mar
3 prepare
1 inspect
2 test[/code][/pre]
I have tried, match, index , vlookup but the problem is this column cannot be hardcoded. can anyone help.


sorry about the confusion on posting earlier on the forum were i was not supposed to.

thanks
 
Thankfully, looks like someone else has already asked a similar question and found an answer!

http://chandoo.org/forums/topic/vlookup-with-multiple-criteria
 
Need to use the INDEX function. Assuming your original table is in A1:E5, and your dropdowns are somewhere else:


=INDEX($B$2:$E%5,MATCH(NumberChoice,$A$2:$A$5,0),MATCH(MonthChoice,$B$1:$E1,0))


Index lets you choose both the row and column to look at, so we use your 2 dropdowns to determine exactly that!
 
Hello,


this is what i have i dont think it is working.


=INDEX(datatable,MATCH(valmilestone,a1:a3),MATCH(valmonth,lstmonth,0))

Input screen

‘

Col A B C D

No Desc Date Month (month is a pull down box)

1 data1 3/4/2012 jan

2 data 2 5/6/2012

5 data 5 7/8/2012

6 data 6 9/9/2012

Data

[pre]
Code: 
A            B     C                  D   E      F        G
No            Desc   Date         Jan   Feb     Mar      April
1            data1   3/4/2012     test  revise  inspect   ship
2            data 2    5/6/2012   ship   test    revise    on-plan
3            data 3               ship    inspect   test      revise
[/pre]
Datatable is the named range for my above data.

Valmilestone is the number choice which is a dropdown at each row in my input screen

A1:a3 refers to number in my data table . valmonth is the month named range and lstmonth is the named range for d2 : g2 from my datatable. Right now when I change the numberchoice I am getting data form the row corresponding to the number but not related to the month.


I am able to lookup data for no, desc and date but the correct data is not getting highlighted for the month.


thanks for your help with this.
 
Hi ,


Please check out whether all your range name definitions are proper.


1. I have defined the following named ranges :

[pre]
Code: 
Data_Table    -   =Sheet1!$A$2:$G$5

List_Month    -   =Sheet1!$D$2:$G$2

valmilestone  -   =Sheet1!$C$13

valmonth      -   =Sheet1!$C$14
Of course valmilestone and valmonth can be anywhere on the worksheet. List_Month is the range which has "Jan" , "Feb" , and so on. Data_Table includes the row and column headers.


The formula itself is :

=INDEX(Data_Table,MATCH(valmilestone,A3:A5)+1,MATCH(valmonth,List_Month,0)+3)
[/pre]
The +1 and +3 are to account for the header row and the initial columns before the months start , in column D.


Narayan
 
My valmilestone is not based on a single absolute value


i have 2 pull downs. month and number


each time the user can change the intent is that lets say that there are 50 rules.

I have chosen that the user displays the top 10 rules relevant for that month.

So it could be he chooses the number as

'1

2

6

50`


once the user chooses the rules the relevant data associated will get populated with the vlookup.


since in vlookup i need to define the column number i am not sure what column number to define because the month is based on the user dynamically changing it.
 
http://speedy.sh/cZKtJ/lookupeg.xlsx


https://docs.google.com/spreadsheet/ccc?key=0AgD23ALrIXLYdHQ5YmdfXzZmX19Qc0o3T1FSSDUwOEE


i have uploaded my file on both these locations i am not sure which one will open.

thanks,
 
A very big thanks to Narayanak for having asked me to revisit my named ranges and Luke for helping me with the forumlas. I got it working . the problem was that my valmilestone was a range of values instead of referring to a single cell.


thanks once again for all your inputs. it is such a powerful feature in excel.


thanks once again
 
Hi ,


I have downloaded your file ; can you specify which addresses you want populated , and with what data ?


You have drop-downs in cells A4:A8 ; the formulae in cells B4:C7 are retrieving some data.


You also have a drop-down in cell D2.


Can you say which cells should have what data based on which combination of drop-downs ?


Do you want D4:D8 to be populated with the result of applying the value in D2 and the values in the cells A4:A8 ? Do you want that D4:D8 should have the values "test" , "test" , "reserve" , "free" ?


Narayan
 
You must log in or register to reply here.
Back
Top