vlook up short cut

T

Tonyk1051

Board Regular
Joined
Feb 1, 2021
Messages
135
Office Version
  1. 2019
Platform
  1. Windows

Dropbox

www.dropbox.com www.dropbox.com

its a simple vlook up, starting from cell c2 in sheet 1, b2 in sheet 1 is the reference, columns B and C from sheet 2 is the array, with 2 and false. The issue as you can see for yourself is I have to do it by date. So 7-22-24 in sheet 1 has to go with 7-22-24 in sheet 2 and theres like 40 dates which makes doing this manually a pain.... is there a short cut? please help
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
A bit of a guess, as you haven't indicated the required results. Is this what you want to do?

ABC
1dateRO #
222/07/20241lizeth
322/07/20242n/a
422/07/20243lizeth
522/07/20244n/a
622/07/20245lizeth
722/07/20246n/a
822/07/20247n/a
922/07/20248n/a
1022/07/20249n/a
1122/07/202410lizeth
1222/07/202411n/a
1322/07/202412lizeth
1423/07/20241danae
1523/07/20242danae
1623/07/20243danae
1723/07/20244danae
1823/07/20245danae
1923/07/202410n/a
2023/07/202412danae
2123/07/202414danae
Sheet1
Cell Formulas
RangeFormula
C2:C21C2=IFERROR(INDEX(Sheet2!C$2:C$15,MATCH(1,(Sheet2!A$2:A$15=Sheet1!A2)*(Sheet2!B$2:B$15=Sheet1!B2),)),"n/a")

ABC
1dateRO #Vendor Liaison
222/07/20241lizeth
322/07/20243lizeth
422/07/20245lizeth
522/07/202410lizeth
622/07/202412lizeth
723/07/20241danae
823/07/20242danae
923/07/20243danae
1023/07/20244danae
1123/07/20245danae
1223/07/20246danae
1323/07/202412danae
1423/07/202413danae
1523/07/202414danae
Sheet2
 
Upvote 0
Solution
StephenCrump said:
A bit of a guess, as you haven't indicated the required results. Is this what you want to do?

ABC
1dateRO #
222/07/20241lizeth
322/07/20242n/a
422/07/20243lizeth
522/07/20244n/a
622/07/20245lizeth
722/07/20246n/a
822/07/20247n/a
922/07/20248n/a
1022/07/20249n/a
1122/07/202410lizeth
1222/07/202411n/a
1322/07/202412lizeth
1423/07/20241danae
1523/07/20242danae
1623/07/20243danae
1723/07/20244danae
1823/07/20245danae
1923/07/202410n/a
2023/07/202412danae
2123/07/202414danae
Sheet1
Cell Formulas
RangeFormula
C2:C21C2=IFERROR(INDEX(Sheet2!C$2:C$15,MATCH(1,(Sheet2!A$2:A$15=Sheet1!A2)*(Sheet2!B$2:B$15=Sheet1!B2),)),"n/a")

ABC
1dateRO #Vendor Liaison
222/07/20241lizeth
322/07/20243lizeth
422/07/20245lizeth
522/07/202410lizeth
622/07/202412lizeth
723/07/20241danae
823/07/20242danae
923/07/20243danae
1023/07/20244danae
1123/07/20245danae
1223/07/20246danae
1323/07/202412danae
1423/07/202413danae
1523/07/202414danae
Sheet2
Click to expand...
StephenCrump said:
A bit of a guess, as you haven't indicated the required results. Is this what you want to do?

ABC
1dateRO #
222/07/20241lizeth
322/07/20242n/a
422/07/20243lizeth
522/07/20244n/a
622/07/20245lizeth
722/07/20246n/a
822/07/20247n/a
922/07/20248n/a
1022/07/20249n/a
1122/07/202410lizeth
1222/07/202411n/a
1322/07/202412lizeth
1423/07/20241danae
1523/07/20242danae
1623/07/20243danae
1723/07/20244danae
1823/07/20245danae
1923/07/202410n/a
2023/07/202412danae
2123/07/202414danae
Sheet1
Cell Formulas
RangeFormula
C2:C21C2=IFERROR(INDEX(Sheet2!C$2:C$15,MATCH(1,(Sheet2!A$2:A$15=Sheet1!A2)*(Sheet2!B$2:B$15=Sheet1!B2),)),"n/a")

ABC
1dateRO #Vendor Liaison
222/07/20241lizeth
322/07/20243lizeth
422/07/20245lizeth
522/07/202410lizeth
622/07/202412lizeth
723/07/20241danae
823/07/20242danae
923/07/20243danae
1023/07/20244danae
1123/07/20245danae
1223/07/20246danae
1323/07/202412danae
1423/07/202413danae
1523/07/202414danae
Sheet2
Click to expand...
Basically column c in sheet 1 should be filled with names or n/a based on the vlook up.
But like I said it’s gotta be by the date. After I manually do it for 7-22-24, i have to do it all over again for the next date.. the cycle repeats -__-
 
Upvote 0
StephenCrump said:
A bit of a guess, as you haven't indicated the required results. Is this what you want to do?

ABC
1dateRO #
222/07/20241lizeth
322/07/20242n/a
422/07/20243lizeth
522/07/20244n/a
622/07/20245lizeth
722/07/20246n/a
822/07/20247n/a
922/07/20248n/a
1022/07/20249n/a
1122/07/202410lizeth
1222/07/202411n/a
1322/07/202412lizeth
1423/07/20241danae
1523/07/20242danae
1623/07/20243danae
1723/07/20244danae
1823/07/20245danae
1923/07/202410n/a
2023/07/202412danae
2123/07/202414danae
Sheet1
Cell Formulas
RangeFormula
C2:C21C2=IFERROR(INDEX(Sheet2!C$2:C$15,MATCH(1,(Sheet2!A$2:A$15=Sheet1!A2)*(Sheet2!B$2:B$15=Sheet1!B2),)),"n/a")

ABC
1dateRO #Vendor Liaison
222/07/20241lizeth
322/07/20243lizeth
422/07/20245lizeth
522/07/202410lizeth
622/07/202412lizeth
723/07/20241danae
823/07/20242danae
923/07/20243danae
1023/07/20244danae
1123/07/20245danae
1223/07/20246danae
1323/07/202412danae
1423/07/202413danae
1523/07/202414danae
Sheet2
Click to expand...
I modified your formula to do 21k lines and I double checked a handful, it looks great. Thanks a lot Steve, you saved me from a grueling couple hrs of manual work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Excel Help
Replies
4
Views
109
Jeffrey Mahoney
Jeffrey Mahoney
P
  • Solved
Count the number of entries per month during the year
2
Replies
12
Views
296
philb99
P
P
  • Question Question
Copy data from one tab to another.
2
Replies
11
Views
196
mumps
mumps
S
  • Question Question
Sales Planners - sync date with days and print in book form
Replies
0
Views
74
ShawnSPS
S
B
  • Solved
Updated Parts list compare
Replies
8
Views
101
etaf
etaf

Forum statistics

Threads
1,226,501
Messages
6,191,399
Members
453,655
Latest member
lasvegasbuffet

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top