What is the lookup formula to search in multiple columns

[Ramadan2512]

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

 

[Fluff]

If the IDs are actual numbers, rather than text, how about

=SUMIFS(C3:I6,B3:H6,B10)

 

[Ramadan2512]

If the IDs are actual numbers, rather than text, how about

=SUMIFS(C3:I6,B3:H6,B10)

that's it, yes only numbers - perfect - thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Ramadan2512]

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

 

[Alex Blakenburg]

Try:

=LET(CombCol,WRAPROWS(TOCOL(B3:I6),2),
     XLOOKUP(B10,INDEX(CombCol,0,1),INDEX(CombCol,0,2),""))

 

[myall_blues]

Can we assume that if the employee name is repeated then the ID number is the same for both names? If yes then try:

=SUMIFS(C3:I6,B3:H6,B10)/COUNTIFS(B3:H6,B10)

 

[Cubist]

Replace Fluff's SUMIF with MAXIFS

=MAXIFS(C3:I6,B3:H6,B10)

 

[Ramadan2512]

Can we assume that if the employee name is repeated then the ID number is the same for both names? If yes then try:

=SUMIFS(C3:I6,B3:H6,B10)/COUNTIFS(B3:H6,B10)

thanks for your reply sometime not the same number

 

[Ramadan2512]

Try:

=LET(CombCol,WRAPROWS(TOCOL(B3:I6),2),
     XLOOKUP(B10,INDEX(CombCol,0,1),INDEX(CombCol,0,2),""))

thanks for your reply but I think the formula is not compatable with my excel version 2021 - it gives me #NAME error