Lookup Row & Column Data

JERRY89
4StarLounger
Posts: 476
Joined: 21 Feb 2016, 02:52

Lookup Row & Column Data

Post by JERRY89 »

Dear All,

I have a file where i need to lookup match row & column data but i use Index function but it fail.Any solution
You do not have the required permissions to view the files attached to this post.

adeel1
2StarLounger
Posts: 145
Joined: 04 Oct 2017, 15:47

Re: Lookup Row & Column Data

Post by adeel1 »

try>

=+INDEX(Sheet2!$C$3:$F$4,MATCH(Sheet1!$B4,Sheet2!$B$3:$B$12,0),MATCH(Sheet1!C$3,Sheet2!$C$2:$F$2,0))

User avatar
TonyE
3StarLounger
Posts: 356
Joined: 24 Jan 2010, 14:24
Location: Buckinghamshire, England

Re: Lookup Row & Column Data

Post by TonyE »

The solution below will only apply if using Excel 365 or Excel on the web.

There is a function in Excel 365 called XLOOKUP that has recently been introduced. This is a powerful lookup function that can be used to replace other lookup functions including INDEX/MATCH. It is currently only available on Excel 365 and the Excel web app.

=XLOOKUP($B4,Sheet2!$B$3:$B$4,XLOOKUP(C$3,Sheet2!$C$2:$F$2,Sheet2!$C$3:$F$4))
Tony

JERRY89
4StarLounger
Posts: 476
Joined: 21 Feb 2016, 02:52

Re: Lookup Row & Column Data

Post by JERRY89 »

Hi Adee...Thanks for the solution :clapping: .. Hi Tony..Great to know that function...Thanks alot. :thankyou: