Function to look up value based on two factors

**oleg232000** · 03-27-2024, 03:10 PM

Hello,

I am trying to devise a formula where I lookup a value from another worksheet. This is easy enough with vlookup, but there are certain sets of data that require two checks. Meaning the value at the end will be determined by two columns of data, rather than one.

Please file attached. I feel like I can do this with IFS, but I am also trying to get a formula/function that can be easily pulled down the list everytime i get the list. Like it needs to be a repeatable formula and not a stand alone to get the value this one time.Book2.xlsx

Thank you

**TMS** · 03-27-2024, 03:38 PM

A few ways (to start)

=INDEX(Sheet2!$C$2:$C$6,MATCH(B2&C2,Sheet2!$A$2:$A$6&Sheet2!$B$2:$B$6,0))

=INDEX(Sheet2!$C$2:$C$6,MATCH(1,(B2=Sheet2!$A$2:$A$6)*(C2=Sheet2!$B$2:$B$6),0))

=FILTER(Sheet2!$C$2:$C$6,(B2=Sheet2!$A$2:$A$6)*(C2=Sheet2!$B$2:$B$6))

=INDEX(Sheet2!$C$2:$C$6,XMATCH(1,(B2=Sheet2!$A$2:$A$6)*(C2=Sheet2!$B$2:$B$6),0))

**DJunqueira** · 03-27-2024, 03:54 PM

With named tables (TCode) you can use this formula.

Formula:

Please Login or Register  to view this content.

And spill array formula

Formula:

Please Login or Register  to view this content.

Function to look up value based on two factors

LinkBack

Thread Tools

Rate This Thread

Display

Function to look up value based on two factors

Re: Function to look up value based on two factors

Re: Function to look up value based on two factors

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Lookup based on two factors

[SOLVED] Due date based on a few factors

Returning a value based on several factors

Function that compares factors and their genders

Outputting factors and prime factors of number into one cell

Multiple Factors in IF Function

[SOLVED] what is the max number of factors in Linest function

Bookmarks

Bookmarks

Posting Permissions