Complex excel lookup formula!!

[jake082014]

I've uploaded my problem as an image in the following, it's a sample excel that lays out the scenario - excel Pictures, excel Images, excel Photos, excel Videos - Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting. So I'm looking for a formula for the red font that spits out the name of the person who is a store owner that owns red paint or home owner that owns red paint, etc. Please help!!!

 

[azumi]

Find example file in this link, hope it works, using array formula (means you need to confirm press CTRL-SHIFT-ENTER button together, ENTER alone will not works)

https://simpan.ugm.ac.id/public.php?service=files&t=2a39e142a4c912be1111b0ae635b13c1

Cheers

 

[jake082014]

Find example file in this link, hope it works, using array formula (means you need to confirm press CTRL-SHIFT-ENTER button together, ENTER alone will not works)

https://simpan.ugm.ac.id/public.php?service=files&t=2a39e142a4c912be1111b0ae635b13c1

Cheers

Thank you so much! Question: for k in the small function, why do you use columns(A1) in cell c14? If I want to copy across the formula, it's ok to have that columns(cell) to go anywhere? should I restrict to the first column?

 

[azumi]

Even better you can use the current cells where you put the first cell fiiled up with formula:

COLUMNS($C$9:$C9) or more convinient for me use =COLUMNS(A1), when you go another column make sure just start with A1

 

[Aladin Akyurek]

I've uploaded my problem as an image in the following, it's a sample excel that lays out the scenario - excel Pictures, excel Images, excel Photos, excel Videos - Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting. So I'm looking for a formula for the red font that spits out the name of the person who is a store owner that owns red paint or home owner that owns red paint, etc. Please help!!!

Posting a sample directly usable in Excel is not difficult. What follows is done with the the last method among: Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste. Such makes everything available within the forum itself.

Given
	Store	Store	House
	John	Bob	Tom
1	Red		Red
2		Blue	Blue
3	Purple	Green
4			Yellow
Needed: C10:D14		Store	House
1	Red	John	Tom
2	Blue	Bob	Tom
3	Purple	John	n/a
4	Green	Bob	n/a
5	Yellow	n/a	Tom

<TBODY>
</TBODY>

C10, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$3:$D$3,
  MATCH(C$9,IF(INDEX($B$4:$D$7,
  MIN(IF($B$4:$D$7=$B10,ROW($B$4:$D$7)-ROW($B$4)+1)),0)=$B10,$B$2:$D$2),0)),
  "n/a")

 

[jake082014]

Posting a sample directly usable in Excel is not difficult. What follows is done with the the last method among: Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste. Such makes everything available within the forum itself.

Given
	Store	Store	House
	John	Bob	Tom
1	Red		Red
2		Blue	Blue
3	Purple	Green
4			Yellow
Needed: C10:D14		Store	House
1	Red	John	Tom
2	Blue	Bob	Tom
3	Purple	John	n/a
4	Green	Bob	n/a
5	Yellow	n/a	Tom

<TBODY>
</TBODY>

C10, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$3:$D$3,
  MATCH(C$9,IF(INDEX($B$4:$D$7,
  MIN(IF($B$4:$D$7=$B10,ROW($B$4:$D$7)-ROW($B$4)+1)),0)=$B10,$B$2:$D$2),0)),
  "n/a")

Thank you so much!! I've already used azumi's formula and it's working perfectly fine. Could you please explain the difference in using yours versus azumi? Or if that's too easy to do then could you explain to me how I can create another formula for identifying duplicates? For example, there cannot be two Stores that use Red paint so if I input Red for John Store and Bob Store, it would say DUPLICATE or somethihng so I can know I need to go back and find the duplicate. I don't need to know exactly where the duplicate is, but I'd like to know that Red was found more than once in the first table. Please and thank you!!

 

[Aladin Akyurek]

Thank you so much!! I've already used azumi's formula and it's working perfectly fine. Could you please explain the difference in using yours versus azumi?

I'm afraid you missed the point of why posted...

Or if that's too easy to do then could you explain to me how I can create another formula for identifying duplicates? For example, there cannot be two Stores that use Red paint so if I input Red for John Store and Bob Store, it would say DUPLICATE or somethihng so I can know I need to go back and find the duplicate. I don't need to know exactly where the duplicate is, but I'd like to know that Red was found more than once in the first table. Please and thank you!!

Try to post (not upload somewhere) a sample here and show what is needed (like I have done for you)...