Looking up value of 2 columns to return a value from a third column

[Curioxity]

Hello,

I'm sure I'm being incredibly dense here but I just can't seem to make this really simple function work.

I have 3 columns that I want to run a formula on - I'm trying to write a formula to look up a value in one column, check another column to see if a specific text appears in it, and if so, return the value from a third column that contains an ID relating to those. So the formula will go in a column for each of those Names to produce their code individually as part of a wider exercise to prep a report that works off several extracts.

ID Name Code Microsoft Apple Ikea
10001 Microsoft M1234
2098 Microsoft M99
30918 Apple A102
09989 Ikea I908

So I'd like to reference the ID column (which will always be unique), check to see if Microsoft appears against that ID, then return the relevant code for that.

=IFERROR(INDEX(G:G,MATCH(H1,'H:H,0)),"")

So G:G is the Name Range
H1 will be a field with the Name - Microsoft for example (there'll be a few columns with that fixed name in it so the reference there will change anyway, H2, H3, etc. to return the code for that in each column
H:H is the Code

That formula doesn't appear to work as I thought it should.

Any help much appreciated, thank you!

 

[Special-K99]

I haven't understood your data layout, so base something on this

With your data starting at row 2
ID in column A
Name in Column B
Code in column C

D1 is search ID
E1 is search Name

=LOOKUP(2,1/((A2:A6=D1)*(B2:B6=E1)),C2:C6)

 

[Fluff]

Is this the sort of result you are after?

+Fluff New.xlsm
	G	H	I	J	K	L	M
1	ID	Name	Code		Microsoft	Apple	Ikea
2	10001	Microsoft	M1234		M1234	A102	I908
3	2098	Microsoft	M99		M99
4	30918	Apple	A102
5	9989	Ikea	I908
6
Data

Also please update you account details to show what version of Excel you are using. This affects what functions you can use.

 

[Curioxity]

Is this the sort of result you are after?

+Fluff New.xlsm
	G	H	I	J	K	L	M
1	ID	Name	Code		Microsoft	Apple	Ikea
2	10001	Microsoft	M1234		M1234	A102	I908
3	2098	Microsoft	M99		M99
4	30918	Apple	A102
5	9989	Ikea	I908
6
Data

Also please update you account details to show what version of Excel you are using. This affects what functions you can use.

Yes that's exactly it sorry I didn't make it very clear to all the posters here, I've updated my account details too.

Many thanks!

 

[Fluff]

Did you try the formula?

 

[Curioxity]

Book1
	A	B	C	D	E	F	G
1	Code	COMPANY	COMPANY ID		Microsoft	Apple	HP
2	109898	Microsoft	1001		1001
3	109898	Apple	999			999
4	787897	Microsoft	1002		1001
5	787897	Apple	998
6	896783	HP	567				567
7	897812	HP	568				568
8	2456	Apple	997			997
9	762	Microsoft	1003		1003
10	762	Apple	996			996
11	762	HP	569				569
12
Sheet1

I don't think it's quite captured it, sorry I've restructured it as we have codes that appear more than once for different companies, but the company ID is what I need to capture and the vlookup will only return the first value which would be fine except there are multiples of the same field to check. Hope that makes sense, apologies for not being very clear at the start.

 

[Fluff]

When using XL2BB you need to select all the entire range, not just a single cell.
Did you try the formulae in post#3?

 

[Curioxity]

Yes that works perfectly thank you very much

 

[Fluff]

Glad we could help & thanks for the feedback.