98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Vlookup for 2 sheet with match 2 parameter | Excel forum
Vlookup for 2 sheet with match 2 parameter | Excel forum
Resolved · Urgent Priority · Version 2010
Fri 13 Oct 2017, 10:57Delegate Sanjay said...
Sanjay has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Vlookup for 2 sheet with match 2 parameter
Hi,
I have to use the Vlookup in between the 2 sheets where I have 2 condition
1:- country name is same
2:- device are same
then use the price of the device.
please let me know how we can use the Vlookup or any other formula.
Thanks
Sanjay
Fri 13 Oct 2017, 11:22Trainer Jens said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi Sanjay,
Thank you for the forum question.
You can do it with Vlookup but Vlookups are not the best solution. Vlookups make your files slow and huge. I have attached an example file where I am using Index Match. You will have to turn the combination to an array formula type the functions in the cell where you need the function, but finish by pressing Ctrl Shift Enter.
In my example I typed:
=INDEX($C$2:$C$10,MATCH(G2:G10&H2:H10,$A$2:$A$10&$B$2:$B$10,0))
in I2 and then pressed Ctrl Shift Enter.
I hope my example makes sense
NB It can takes a little time to see the attached file. If you cannot see wait 10 minutes and refresh.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
multi Lookup.xlsxFri 13 Oct 2017, 11:58Delegate Sanjay said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi Jens,
its not working out for me ,will it be ok if ill send my files to you on your mail id?
Thanks
Sanjay
Fri 13 Oct 2017, 12:15Trainer Jens said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi Sanjay,
That's ok but please tell me which columns are the Lookup columns (lookup values) and where you want to lookup up the lookup values and which columns and which information you want to get in the destination sheet (the columns).
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 13 Oct 2017, 12:41Delegate Sanjay said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi,
I have 2 sheets.
sheet1 and sheet 2
I have to get the column J value in sheet 2 and to get the value i have to mach 2 things between the 2 sheets as follows
sheet 2 has rows (Countries) from E4 to AX which should be match the countries value in sheet 1 in columns A4 to A858.
another things once country is match then i have to match the device value from sheet 2 column A5 to A997 with value in sheet 1 column C4 to C997.
please let me know if you need any more information.
Thanks
Sanjay
Fri 13 Oct 2017, 13:00Trainer Jens said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi Sanjay,
I haven't got the file, but I will let you know if I need more information when I have the file.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 13 Oct 2017, 13:13Trainer Jens said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi Sanjay,
I have received the files.
I have a question.
Do you have the sheets in the same workbook or in two different workbooks.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 13 Oct 2017, 13:35Delegate Sanjay said...
RE: Vlookup for 2 sheet with match 2 parameter
both are in a different workbook
Fri 13 Oct 2017, 14:22Trainer Jens said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi Sanjay,
You do not have any match between the mentioned columns and rows, but if you had you could have done it with a Sumifs.
In sheet2 C5 following formula should do the job:
=SUMIFS(Sheet1.xlsx!J$4:J$997,Sheet1.xlsx!$A$4:$A$997,$C4,Sheet1.xlsx!C$4:C$997,$A5)
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 13 Oct 2017, 15:28Delegate Sanjay said...
RE: Vlookup for 2 sheet with match 2 parameter
Formula is working but not giving any value
Fri 13 Oct 2017, 15:46Trainer Jens said...
RE: Vlookup for 2 sheet with match 2 parameter
Hi Sanjay,
The formula will work if you have matches.
If you think you have a match between two cells try in a blank cell to type =A1=B1 if you think A1 and B1 match. If Excel return TRUE Excel see that the the two cells match. If Excel returns FALSE you do not have matching cells.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 20 Oct 2017: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Display developer tab - Excel 2010a. In Excel, click on the File tab |
