Get row number where three columns contain specific values

[agni452]

I need to retrieve the row number to use in an ADDRESS function where the value in A is "L", the value in B is "1", and the value in C is "5" at the same time. I found this array formula that allows me to check two columns' values, but how do I get it to check a third and return the row number that contains all three?

{=MATCH("L",IF(B2:B7="1",A2:A7),0))}

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

Quote the 1 & 5 withing double quotes if needed..

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[agni452]

I tried

{=MATCH(1,('# A-B'!$D:$D=TEXT($A2,"?"))*('# A-B'!$E:$E=TEXT($B2,"?"))*('# A-B'!$F:$F="1"),0)}

(entered as an array formula), but all I get is #N/A

[:) Sixthsense :)]

It should work...

Instead of TEXT($A2,"?") use Trim($A2) which will convert the number to text.

Check whether you are having the same values in the referred range.

Please don't use Array Formula for whole column instead restrict it for a range..

[agni452]

For some reason it doesn't work, even though I know that there is a row that fits the bill. I've moved on to trying VBA to create my own function that takes in 3 ranges and the respective values I want found in each of those ranges, then loops through each until all three are found. It then returns the row number where it found all three (if it didn't find anything, it returns -1). That's the idea, but my first pass at it isn't working (it reports back the row in which it found the first value in the first range). Because this function may be used quite a bit throughout my workbook, I'd like it to stop searching as soon as it finds the match. Here's my first pass:

Please Login or Register  to view this content.