# HLOOKUP AND MATCH in one formula?

#### caroline.vanbommel

##### New Member
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?

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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)

oops

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

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.

Replies
1
Views
147
Replies
0
Views
207
Replies
3
Views
271
Replies
4
Views
372
Replies
6
Views
461

1,219,687
Messages
6,149,716
Members
450,910
Latest member
DianeG

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back