Excel Lookup more than one column.

[John1Chr]

I am comparing data in wksht_tab_A to another wksht_tab_B. The three fields are accept date, Project number, and amount. If all three agree I want to put the Month number from wksht_tab_B in the new column on wksheet_tab_A. Is there a quick formula for this?


Wksht_tab_A New Column
Accept date Project Number amount (from comparing B)


Wksht_tab_B
Accept date Project Number amount

 

[SkipVought]

Hi,

Data/get External Data... on sheet B, as a Paramter Query, where your 3 values are the parameters.

Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

Here's a worksheet (non-VBA) solution:

Concatenate the three columns into a helper column, then use that for the lookup.

For exapmple, you might use the following in D1:
=A1 & B1 & C1

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[John1Chr]

I appreciate the responses. SkipV, I spent time trying to get MS Query to work on my computer. I couldn't get it to work within the spreadsheet. (I wasn't able to query off of the data in excel.) Obviously, it's because of my lack of experience with this feature of excel.

AHiggins, I used your method and was trying to see if there was a function in excel to look at the fields separately.

Anyways, I resolved the problem and realized that I need to get more versed on the MSQuery function of Excel.

Thanks.

 

[SkipVought]

In the Add Tables Window in the QBE Editor, click the OPTIONS button and check the Show System Tables checkbox. (ALL should be checked)

Skip,
[sub]
[red][/red]
[/sub]

 

[willir]

BrGenCAJ

Concatenate should meet your needs.

I use the following formual / function, comapre four (complex) fields in the row above to the current row to check for duplicate rows. (Numerous rows are imported from an SQL database, and some times rows are imported twice by the end users.

=IF(CONCATENATE(D4,E4,G4,I4)=CONCATENATE(D3,E3,G3,I3),"Dup","")

The trick is that both sides of the comparison have to be concatenated. ALSO, if you are mixing numbers, dates and text, you may have to add complexity by ensuring that the formats are the same.

In my example, D4 is a date, G4 is an integer and remainder are text, but I use the same formatting so my comparison is fairly straight forward.

Richard

 

[Rofeu]

Hi,

you can use the following array formula:

{=OFFSET($F$1,LARGE(--($C$1:$C$5000=A$1)*--($D$1:$D$5000=A$2)*--($E$1:$E$5000=A$3)*ROW($F$1:$F$5000),1)-1,0)}

Somewhat more explanatory:

{=OFFSET(ReturnRangeCell1,LARGE(--(LookupRange1=LookupValue1)*--(LookupRange2=LookupValue2)*--(LookupRange3=LookupValue3)*ROW(ReturnRange),Nth)-1,0)}

the LookupRanges and ReturnRange must be the same size and can NOT span an entire column.

you can repeat *--(LookupRange3=LookupValue3) as you need more criteria

Nth is the order of greatness you want the value reutn for. i.e. if there's more than 1 row that meets the criteria, 1 will the return the greatest return value, 2 the second greatest, etc.

ReturnRangeCell1 is the first cell of your ReturnRange.

Enter this formula as an array-formula (with crtl-shift-enter)

Cheers,

Roel