Vlookup with multiple conditions

Abgar

Active Member
Joined
Jun 20, 2009
Messages
265
Hi all,

Hoping you can help here please. I have a table on sheet called "RawData". This sheet contains multiple rows / columns of data, and i am looking to use a Vlookup to:

* Lookup a value in column B of the table. This table may contain multiple entries which match in column B, but each would have a different value in column 5.
* I then want to crossreference the column with a specific value in column, and if found, then return a date contained in column 4.

I can't edit the data in the table, and so can't create a helper row in this table to use a Vlookup.

OwnerRef NumberClientDue DateAction
TOMWER000600123/10/2019Second Action
TOMWER000600129/10/2019First Action
TOMWER000600114/10/2019Third Action

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

So, if using the above as an example, i need a formula that i can input in a random cell of my choosing that will find where "Ref Number" = WER0006001 & "Action = First Action", and then populate the corresponding date (i.e. 29/10/2019).

I've seen some information on using a Match / Index, which results in an array formula, but i can't get my head around the parameters to use (i.e how to actually use it). Can someone provide an example of how i could use it in the above? Or an alternative approach i could use?

Also worth noting, the formula would be used in excess of 200 times in one workbook, so im not sure if an array formula would be suitable?

Appreciate any help you can give.

**Edit - sorry, Im on a work computer, so I can't figure out to properly input a table, so hopefully the above shows ok.

Cheers,
ABGar
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Assuming that your lookup cell is A6 and you are entering formula in B6.

concatenating the cell will help you.

=INDEX($D$2:$D$4,MATCH(A6,$B$2:$B$4&$E$2:$E$4,0))

The blue part of the formula is.... the target data you want to extract.
The Green part of the formula is ..... text or cell reference -- for the match you are looking for
The red part of the formula is ...... range in which you have to lookup.
 
Upvote 0
How about


Book1
ABCDEFIJK
1OwnerRef NumberClientDue DateActionWER0006001
2TOMWER000600123/10/2019Second ActionFirst Action29/10/2019
3TOMWER000600129/10/2019First Action
4TOMWER000600114/10/2019Third Action
5
Lookup
Cell Formulas
RangeFormula
K2=INDEX(D2:D4,MATCH(J1&"|"&J2,INDEX(B2:B4&"|"&E2:E4,0),0))
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top