Looking up tool

[btardo01]

Good morning:

I have a set of data that looks like this:

Sheet 1:

   A     B      C
300101 410000
300101 420000
300101 430000
300101 462500
300101 500015
300101 500015
300101 500015
300101 500015
300101 500025
300101 500040
300101 500040

Sheet 2:

 A                     B     C       D
NCF                 410000 410010 410099
Commission Expense  408000 408010

I'd like Column C of Sheet 1 to have a formula for each line that looks at column B of sheet 1 then goes to sheet 2 and finds that value in range B1:D2 and returns the value from column A of sheet 2.

Can you help me with this, please?

thanks

 

[btardo01]

I don't think I can use combination of Index and Match because I am using multiple columns, right?

Was thinking about DGET, but thats not working either.

Any suggestions?

thanks

 

[btardo01]

Another problem I am having is that I need it to return exact matches only.

 

[btardo01]

Good morning

just stoping by to see if anyone had any suggestions for me.

thank you

 

[T. Valko]

Good morning

just stoping by to see if anyone had any suggestions for me.

thank you

Maybe this array formula** entered on Sheet1 in cell C2 and copied down as needed:

=IF(COUNTIF(Sheet2!B$1:D$2,B2),INDEX(Sheet2!A$1:A$2,MAX(IF(Sheet2!B$1:D$2=B2,ROW(Sheet2!B$1:D$2)-ROW(Sheet2!B$1)+1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[btardo01]

thanks so much!

 

[T. Valko]

thanks so much!

You're welcome!