What is the lookup formula to search in multiple columns

Ramadan

Banned User
Joined
Jan 20, 2024
Messages
93
Office Version
  1. 2021
Platform
  1. Windows
I have a worksheet table with 4 columns containing employee name and 4 columns for ID number for this employee

simply and I need a formula to add the employee ID number which is next to the name if the cell match any cell of the employees names in the 4 columns ("B , D , F or H")
for example as you can see in the attached screenshot

in cell C10 I need to add a formlua which is (if B10= any of names in columns (B,D,F,H) then add the ID number of this employee in the cell C10)

please I need to know what is the lookup or vlookup or xlookup or any other formula to do that
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    44.6 KB · Views: 21

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
If the IDs are actual numbers, rather than text, how about
Excel Formula:
=SUMIFS(C3:I6,B3:H6,B10)
 
Upvote 0
You're welcome & thanks for the feedback.
But I faced one serious issue which is if the employee name was repeated more than once, the formula give a wrong result because it add the ID number as a SUM while I don't want that
 
Upvote 0
Try:
Excel Formula:
=LET(CombCol,WRAPROWS(TOCOL(B3:I6),2),
     XLOOKUP(B10,INDEX(CombCol,0,1),INDEX(CombCol,0,2),""))
 
Upvote 0
Can we assume that if the employee name is repeated then the ID number is the same for both names? If yes then try:

Excel Formula:
=SUMIFS(C3:I6,B3:H6,B10)/COUNTIFS(B3:H6,B10)
 
Upvote 0
Replace Fluff's SUMIF with MAXIFS
Excel Formula:
=MAXIFS(C3:I6,B3:H6,B10)
 
Upvote 0
Solution
Can we assume that if the employee name is repeated then the ID number is the same for both names? If yes then try:

Excel Formula:
=SUMIFS(C3:I6,B3:H6,B10)/COUNTIFS(B3:H6,B10)
thanks for your reply sometime not the same number
 
Upvote 0

Forum statistics

Threads
1,225,725
Messages
6,186,646
Members
453,367
Latest member
bookiiemonster

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