# excel formula if and vlookup together

#### tonyjyoo

##### Board Regular
Hello,

Need some help with combining an IF statement with VLOOKUP.

The following is an example table:

 Company ID Company Name Transaction Type Deposits Withdrawals 912 B 009MUTUAL FUNDS DEBITS 1010 -9900 018 DTCC PAYMENT AXIS 54,843.80 0 911 A 009MUTUAL FUNDS DEBITS 100 0 018 DTCC PAYMENT AXIS 400 0 930 D 009MUTUAL FUNDS DEBITS 5000 -10 018 DTCC PAYMENT AXIS 500 -100 920 C 009MUTUAL FUNDS DEBITS 9999 0 018 DTCC PAYMENT AXIS 99 -999

<tbody>
</tbody>

On a separate excel file, how can I write a formula so that IF column A (company ID) = one of them, THEN VLOOKUP "009MUTUAL FUNDS DEBITS" correspondingly?

So for example, if I wanted a formula for Company B (ID 912), I would want the formula something like =IF(A:A="912", VLOOKUP("009MUTUAL FUNDS DEBITS) so that it will give me 1010.
****** id="cke_pastebin" style="position: absolute; top: 260px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">On
 018 DTCC PAYMENT AXIS

<tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### MattH1

##### Board Regular
Try this and let me know if it works:

Code:
``=IF(MATCH(912,[Book1]Sheet1!\$A:\$A,0)>0,VLOOKUP(912,[Book1]Sheet1!\$A:\$E,4))``
Change Book1 and Sheet1 to match your file name and sheet.

Edit: This will yield you #N/A results if it's NOT found since the Match won't return a number but rather an #N/A.
To get rid of this is beyond me, it would have to be a completely different formula (I would just filter out all #N/As at the end and remove them.)

Last edited:

#### tonyjyoo

##### Board Regular
Try this and let me know if it works:

Code:
``=IF(MATCH(912,[Book1]Sheet1!\$A:\$A,0)>0,VLOOKUP(912,[Book1]Sheet1!\$A:\$E,4))``
Change Book1 and Sheet1 to match your file name and sheet.

Edit: This will yield you #N/A results if it's NOT found since the Match won't return a number but rather an #N/A.
To get rid of this is beyond me, it would have to be a completely different formula (I would just filter out all #N/As at the end and remove them.)

This works but how can I incorporate it to sum up everything it finds? SOmetimes, there are several deposits. Would this be a sumif?

Replies
3
Views
479
Replies
2
Views
304
Replies
4
Views
144
Replies
2
Views
288
Replies
3
Views
787

1,190,916
Messages
5,983,572
Members
439,850
Latest member
suhailrocks786

### 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