Index match using drop down list

**tenjim** · 05-03-2016, 11:30 AM

Hi All,

I have been trying to create a index match function on two drop down list with no success.

Please see the excel file in dropbox for Cell F7.

https://www.dropbox.com/s/ac6e2jf00z...load.xlsx?dl=0

Any help would greatly appreciated, I been at this for past two days now.

Cheers,

**mikeTRON** · 05-03-2016, 11:47 AM

Assuming you change the FEEDERS column in table 14 to "Feeder" to fit with your data validation then you can use the following formula.

=VLOOKUP(B6,'Table 14'!$B:$E,MATCH('Table 14 Demand load calc.'!G6,'Table 14'!B2:E2,0),FALSE)

Also your Watt/Sqm does NOT need to be entered as an array.
Just copy and paste this in and hit enter.

=VLOOKUP(B6,'Table 14'!B:E,2,FALSE)

Also I would recommend correcting the spelling of industrial and creating a NEW list for Feeder and Service AND the Building types, so you can have them in alphabetical order. then you can just use your new lists for the validation instead of the raw data like you are doing now.

**tenjim** · 05-03-2016, 11:59 AM

wow it works, but why it is from feeders to feeder then it will work?

Thank you.

**mikeTRON** · 05-03-2016, 02:56 PM

Because I thought one list said FEEDER but the column header says FEEDERS but I think I deleted an s on accident. As long as the names are exactly the same in the data validation AND the data you are golden. Look at how I corrected INDUSTRIAL in the attached workbook for the data validation but NOT in the raw data that you are looking up off of. You will need to correct the raw data and it will work.

The match portion of the formula

MATCH('Table 14 Demand load calc.'!G6,'Table 14'!B2:E2,0)

is looking for the drop down in G6 and looking for that EXACT text in the cells B2 then C2 then D2 then E2. and it will return the number that matches so the vlookup knows how many columns to move to the right.

An Index(Match()Match()) would work as well using the same concept but the names have to be EXACTLY the same as well.