Forum Discussion
Solved
VLOOKUP, INDEX and MATCH - can't get them to work
Hi, I'm trying to set up an automatic query that when a code is entered a data dump of information is searched to find the risk ratings for that code in 3 different counties. The data dump will be ...
You could try this, if you don't have office 365, then you'll need to hit Ctrl+Shift+Enter after keying into the formula bar:
=INDEX('County Data Dump'!$H$2:$H$1846, MATCH(1, ('County Data Dump'!$D$2:$D$1849=$B3)*('County Data Dump'!$B$2:$B$1849=C$2), 0))
hi JMB,
i like the solution, as i am new to this thing. i will be appreciate if you clear my doubt, what this (*) asterisk meant in match function arguments. does it means as AND operator?
i like the solution, as i am new to this thing. i will be appreciate if you clear my doubt, what this (*) asterisk meant in match function arguments. does it means as AND operator?
Yes, it works as an AND operator.
('County Data Dump'!$D$2:$D$1849=$B3) returns an array of TRUE/FALSE values.
('County Data Dump'!$B$2:$B$1849=C$2) also returns an array of TRUE/FALSE values.
Then, these arrays are multiplied together to get an array of 1's and 0's (TRUE=1 and FALSE=0, so you get a 1 where the elements of both arrays were TRUE).A simple example, if I were trying to return the value from column C where column A="c" and column B="i"