Lookup Column number in a table based on a cell value

**electricmice** · 08-21-2012, 01:53 PM

In the summary tab I am trying to show the value of the keyword rank for the specified date. I tried using Hlookup and index/match but was unable to pull the column number for the date within the header. What is the best approach to solve this problem?

**oeldere** · 08-21-2012, 02:14 PM

Maybe with a pivot table.

**Søren Larsen** · 08-21-2012, 02:21 PM

In that case: Delete!

**electricmice** · 08-21-2012, 02:23 PM

The visits seemed to be working, it was the rankings that I was having trouble with, which was a blank column on the summary tab.

**dilipandey** · 08-21-2012, 02:24 PM

Hi electricmice,

Welcome to the forum.

Use the below array formula:-
{=INDEX(rankings[#All],MATCH(Summary!$B$2&Summary!$A5,rankings[Engine]&rankings[Keyword],0),MATCH(Summary!$B$1,rankings[#Headers],0))}

Visits and Rankings.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**electricmice** · 08-21-2012, 04:00 PM

That did it! Thanks! I noticed you converted all the dates, is there a quick way to go about that process, it seemed to want to increment by year instead of by day when I attempted to drag across.

**electricmice** · 08-21-2012, 04:28 PM

DILIPandey,

when i changed the date the data stopped pulling, any idea what could cause this?

**dilipandey** · 08-22-2012, 01:08 AM

Hi electrimice,

You need to make the date in date format (not in text format)... to convert the text dates in date format .. type 1 somewhere and copy that.. now multiply the dates by this 1 (pastespecial / multiply)

To check if dates are equal in both the place, just copy one of them to the next of other and compare them with "=" sign.. if they are really equal, then only formula will work.

Regards,
DILIPandey

<click on below 'star' if this helps>