Formula using match

[blandreth]

Hello Excel team.

I'm looking for a simple formula that matches numbers between sheets and displays a value from another cell from one of the sheets.

For example I have a number in B3 on sheet 2(B3 is constant-$B$3). I want the formula to search for the same number in sheet 1, column B and then display the value that is in the same row on sheet 1 in column E.

I'm sorry if this is a simple request, but I haven't used the match function, ever.

I appreciate the help.

Brian

 

[KillerOfGiants]

Which version of excel are you (and anyone else that may need this) use? And can there be more than one result?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX(Sheet1!$E$2:$E$1000,MATCH(B3,Sheet1!$B$2:$B$1000,0))

 

[blandreth]

Which version of excel are you (and anyone else that may need this) use? And can there be more than one result?

Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20282) 64-bit

 

[blandreth]

Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20282) 64-bit

Sorry - forgot to answer the second question. The result will always be in the same row as the matched number on sheet 1 to the constant in sheet 2.

 

[KillerOfGiants]

=XLOOKUP('2'!$B$3,'1'!$B$1:$B$100,'1'!$C$1:$C$100)

You won't need the sheet reference if the formula is on that sheet, for example: if the formula is on sheet 2, you can eliminate '2'! and just leave the $B$3.
The $B$1:$B$100 contains the range where you value is to be matched, and here, column C is your result array.
If you have your lookup table in an excel table, can even change this more (and not be constrained to be inclusive of the necessary rows).

 

[blandreth]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX(Sheet1!$E$2:$E$1000,MATCH(B3,Sheet1!$B$2:$B$1000,0))

Thanks for the response. I updated my account details as suggested. Formula worked for what I needed.

 

[Fluff]

I updated my account details as suggested.

Thanks for that.

Formula worked for what I needed.

Glad we could help & thanks for the feedback.