Lookup formula working for one column but not another with similar data. Help needed.

**bennunn** · 03-09-2013, 10:03 AM

HI all,

I have a formula that calculates the most commonly occuring string value in a long column:

=INDEX('The List'!C$2:C$4999,MATCH(LARGE(COUNTIF('The List'!C$2:C$4999,'The List'!C$2:C$4999),1),COUNTIF('The List'!C$2:C$4999,'The List'!C$2:C$4999),0))

But when I try to use a similar formula to look up the next column, it returns a '#N/A' value.

=INDEX('The List'!D$2:D$4999,MATCH(LARGE(COUNTIF('The List'!D$2:D$4999,'The List'!D$2:D$4999),1),COUNTIF('The List'!D$2:D$4999,'The List'!D$2:D$4999),0))

I can't understand why it works with one column and not another. The data is all string in both, and the cells are formatted as 'TEXT'.

Thanks

**benishiryo** · 03-09-2013, 10:07 AM

hi bennunn, welcome to the forum. did you press CTRL + SHIFT + ENTER? 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.

if it doesnt work, upload a sample file.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Tony Valko** · 03-09-2013, 10:21 AM

Try this array formula**:

=INDEX('The List'!D$2:D$4999,MODE(MATCH('The List'!D$2:D$4999,'The List'!D$2:D$4999,0)))

Assumes no empty cells within the range.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**bennunn** · 03-09-2013, 10:23 AM

CTRL+SHIFT works, thanks!

Lookup formula working for one column but not another with similar data. Help needed.

LinkBack

Thread Tools

Rate This Thread

Display

Lookup formula working for one column but not another with similar data. Help needed.

Re: Lookup formula working for one column but not another with similar data. Help needed.

Re: Lookup formula working for one column but not another with similar data. Help needed.

Re: Lookup formula working for one column but not another with similar data. Help needed.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions