HLOOKUP AND MATCH in one formula?

[caroline.vanbommel]

Hi,

I have a HLOOKUP formula eg.
=HLOOKUP(B4,'[anotherfile.xls]Sheet1'$B$2:$BO$220,219,FALSE
B4 is an sub account of A2

I want the 219 to be replaced the following formula,
=MATCH(A2,'[anotherfile.xls]Sheet1!$B$2:$B$250,FALSE)
A2 is an account name

the point of this formula is that this account name row location changes from sheet to sheet and i dont want to manually fill it in each time over and over.

Does anyone know how to properly combine these two formulas in one?

 

[onlyadrafter]

Hello

Is it not just

=HLOOKUP(B4,'[anotherfile.xls]Sheet1'$B$2:$BO$220,MATCH(A2,'[anotherfile.xls]Sheet1!$B$2:$B$250,FALSE),FALSE)

 

[caroline.vanbommel]

oops

I tried this, but i kept on getting errors
I just noticed i forgot to add an extra set of parenthesis!
Oops, thnx

 

[Andrew Poulsom]

MATCH will return a row index. Is that what you want? Or is it something like?

INDEX('[anotherfile.xls]Sheet1!$A$2:$A$250,MATCH(A2,'[anotherfile.xls]Sheet1!$B$2:$B$250,FALSE))

That looks up A2 in column B and returns what's on the same row in column A.