vb vlookup using 2 columns

[cooket4]

Hi

I am trying to do a vlookup using 2 columns i.e first name , last name to return a day

I have the vb code for looking up the first and second columns, but dont know how to use them together to return the third column.

An example is below:

a b c
1 adam smith Monday
2 adam jones Tuesday
3 steve smith Wednesday
4 steve jones Thursday

Id like to lookup columns A and B to return C. I wont post my code incase I need something completely different!

Thanks in advance

[smuzoen]

You could use an array formula

Please Login or Register  to view this content.

This would place the formula into A8 to find the value in colulmn D when matching Adam (Column B) and Smith (Column C)

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[cooket4]

Thanks for the reply

I have got it to work in excel, but I have tried to adapt it to my vb userform

using the same example data - on sheet "Rota"

a b c
1 adam smith Monday
2 adam jones Tuesday
3 steve smith Wednesday
4 steve jones Thursday

Please Login or Register  to view this content.

I get an error sayng the argument "match" is not optional.

Thanks

[smuzoen]

I just cannot get the formulas to work in VBA - here is a set of non array formulas that will do the same
=INDEX(C1:C4,MATCH(1,INDEX((A1:A4="adam")*(B1:B4="jones"),0,1),0))
=INDEX(C1:C5,SUMPRODUCT((A1:A5="adam")*(B1:B5="jones")*ROW(B1:B5)))
One option would be to write the value to the spreadsheet using the following then read it back into the textbox

Please Login or Register  to view this content.

or use a lot more code using FIND to get the answer

Please Login or Register  to view this content.

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[Winon]

Hi Guys,

Try this. I have used Column G as demostration. Maybe you can adjust to suit. If any day is left out, the word "Empty" will show in that specific row:

Copy this Code to a Module, and test it.

Please Login or Register  to view this content.

Hope it helps!

[smuzoen]

This was doing my head in - the following should work

Please Login or Register  to view this content.

Since I did not set up comboboxes you should just have to try

Please Login or Register  to view this content.

I assume Cmb_First_Name and Cmb_Last_Name are comboboxes?

Let me know how you get on

[Winon]

Hello cooket4,

May I suggest you upload a sample Workbook of what you have, and an explanation as to what exactly you want. Doing so will help us to focus on the actual issue, without assuming any possible solutions which may be way off track.

@smuzoen,

Re:

Cmb_Last_Name = "Jones": Cmb_First_Name = "Adam"

How would that help if the names are "Zabor" and "Charlie" ?

Just wondering...

[smuzoen]

@ Winon
That is what error handling and code is for - he asked how to incorporate the Comboboxes (I assume) into a formula with worksheet functions which I supplied. He said he was using a userform so obviously the variables are controls

Please Login or Register  to view this content.

I had already posted an answer along the lines of your suggestion

Please Login or Register  to view this content.

The point was how to incorporate userform controls into the formula.

[Winon]

@ smuzoen,


Re:

Please Login or Register  to view this content.

Thanks for responding. I do know about ErrorHandling, and this pretty much completes your Code, without leaving cooket4 wondering what is happening if nothing was found.

I also believe that you may be very close with what you are assuming cooket4 wants.

Hope you get a good Rep for your efforts!

@ cooket4,

It would be appreciated if you could confirm if your problem was resolved.

[cooket4]

Hi all

Thanks for the suggestiions. I will be trying these out today or if not tomorrow. I will let you know when I get it working

Thanks again

[cooket4]

Hi

I have used Smuzoen's 'alot more code' FIND. It works like a charm!

Thank you