Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course
VLOOKUP IF CALCULATION
HI
Could someone please help on the following:
I have 2 sets of data in different tabs which contain multiple columns, but essentially I want to check if its a Buy in 1 Sheet, that its a Sell in the other sheet for the same deal reference. So I need to vlookup the deal ref in the other sheet and then check the Buy/Sell, so I can catch any instances, where both sheets say Buy or Sell. This part I assume is where an IF formula comes in to play.:
Sheet 1
E2 will have the calculation
D2 states Buy
M2 has the Deal Reference
Sheet 2
Column AP will have the deal reference somewhere (so I believe a VLOOKUP will be required
Column Z will have the corresponding Sell
Your help would be much appreciated!
Thank you!!
RE: VLOOKUP IF CALCULATION
Hello Adrian,
Thank you for your question. I notice that you have attended VBA training with us. If you need to use VBA to do this task, please write to us again.
If you simply want to do this in Excel, I would recommend using Index and Match to get the results you want, simply because it seems that the columns from which you want to extract the results are to the left of your Deal Reference columns. Vlookup wouldn'be able to look at columns to the left of the Deal Reference columns. Index and Match can do this.
I have prepared and attached a simple workbook with two sheets vaguely similar to your data. On Sheet 1, there are Index and Match formulas down column C which look at the data on Sheet 2. They indicate the Deal References where it says "Sell". You will notice that I have mixed up the references on Sheet 2, so it doesn't matter if they aren't in the same order.
I hope this helps. If not, please feel free to ask again.
Kind regards
Marius Barnard
Excel Trainer
Attached files...
Buy Sell Index and Match.xlsxRE: VLOOKUP IF CALCULATION
Hi Marius
Thank you veyr much! I do wnat ot resolve this via Excel and not VBA, and I shall be taking one of your Excel courses soon!
Unfortunately, I don't think I explained it well/don't quite understand your answer. I require this:
SHEET 1 SHEET 2
ColA ColB COLM COLF
B/S REFERENCE B/S REFERENCE
BUY 1 SELL 2
BUY 2 SELL 1
SELL 3 BUY 5
SELL 4 BUY 6
SELL 5 BUY 4
SELL 6 BUY 3
BUY 7 BUY 7
So in column C in sheet one, it looks up reference Column B in Sheet 1, finds that reference in Column F in Sheet 2, and compares the associated Buy/Sell. If they are opposite (ie number 6), it should return True, if they are the Same (ie number 7) it should return false.
I hope this makes it a bit clearer, sorry for any confusion
Kind regards
Adrian
RE: VLOOKUP IF CALCULATION
Hi Adrian,
I have created a solution based on the data you provided, using If with Vlookup. It works quite well. I have attached the file.
Kind regards
Marius
Attached files...
RE: VLOOKUP IF CALCULATION
Hi Marius
Could I please send you my document seperately as the data is sensitive...When I enter your formula, it comes up with #VALUE!, but I have tried checking/changing the format but it still keeps creating this issue.
If thats possible, please advise the email address to send it too
Kind regards
Adrian
RE: VLOOKUP IF CALCULATION
Hi Adrian,
You're welcome to send the file to forum@stl-training.co.uk. I'll have a look at it.
Regards
Marius
