AdibKhaldi
New Member
- Joined
- Dec 4, 2018
- Messages
- 2
Hi,
I have a data table to which I'm trying to do a vlookup to return all the columns of a matching lookup value, which I was able to do, but only one matching row would appear, while I want all matching rows (and not only the first match). Also, another issue I have is that a vlookup will only lookup the left most column while my lookup value is in the 3rd (City), so I suspect that Index+Match function would do? I couldn't paste a screenshot of the sheet, so here it is:
<tbody>
</tbody>
Given that I want it to return multiple columns, I created the following formula : {=VLOOKUP("HALIFAX", D2:H12, {1,2,3,4,5}, FALSE)}
Also, if it would possible to have more than one lookup value, for example HALIFAX and MONTREAL, I don't know if a VBA code would be more appropriate in case no formula can execute it?
Thank you very much!
A.K
I have a data table to which I'm trying to do a vlookup to return all the columns of a matching lookup value, which I was able to do, but only one matching row would appear, while I want all matching rows (and not only the first match). Also, another issue I have is that a vlookup will only lookup the left most column while my lookup value is in the 3rd (City), so I suspect that Index+Match function would do? I couldn't paste a screenshot of the sheet, so here it is:
Category | Client Number | City | Postal Code | Province | Office | # | |
1 | 11334455 | HALIFAX | C0A1K2 | NS | 1 | 1 | |
2 | 11445566 | HALIFAX | B3C7V10 | NS | 2 | 1 | |
2 | 11556677 | MONTREAL | C0A1K3 | QC | 2 | 1 | |
3 | 11667788 | MONTREAL | B3C7V11 | QC | 3 | 1 | |
3 | 11778899 | MONTREAL | C0A1K4 | QC | 3 | 1 | |
4 | 11890010 | TORONTO | B3C7V12 | ON | 4 | 1 | |
4 | 12001121 | TORONTO | C0A1K5 | ON | 4 | 1 | |
5 | 12112232 | VANCOUVER | B3C7V13 | BC | 5 | 1 | |
5 | 12223343 | VANCOUVER | C0A1K6 | BC | 5 | 1 | |
Category | Client Number | City | Postal Code | Province | Office | # | |
Return here: | |||||||
<tbody>
</tbody>
Given that I want it to return multiple columns, I created the following formula : {=VLOOKUP("HALIFAX", D2:H12, {1,2,3,4,5}, FALSE)}
Also, if it would possible to have more than one lookup value, for example HALIFAX and MONTREAL, I don't know if a VBA code would be more appropriate in case no formula can execute it?
Thank you very much!
A.K