Vlookup / hlookup

**Coconutwei** · 06-30-2011, 05:12 AM

Greeting,

First of all, I had tried SEARCH function. However, most samples are being used in .xlsx, while I do not have the access of that file. So if there's any sample reply, please use .xls.

I managed to learn the use of HLOOKUP, =HLOOKUP(F2,B:E,2,FALSE)
However, it's impossible for me to type in manually for 10k of entries.
I'm seeking for a solution to click and drag for fill in for the rest of the row.

Appreciate any helps, or even better if there's any other formula I could use here.

p/s : i had a feeling i need to use INDEX and MATCH function?

Thanks and Regards.

**martindwilson** · 06-30-2011, 05:31 AM

you could just change your hlookup to(starting in g2)

=HLOOKUP(F2,B:E,ROW(A2),FALSE)

**Coconutwei** · 07-01-2011, 03:48 AM

I got solution. Apparently most advised me to use INDEX and MATCH.

=INDEX(B2:E2,MATCH(F2,$B$1:$E$1,0))

I'm not familiar with the use of symbol "$". But I have to assume it's used to lockdown the variable of the cell. So when I click and drag, the formula would works.

Thanks martindwilson.