Lookup formula

[Nicolajay24a]

Hello so I need to create a lookup cell in excel. There are multiple tabs of information but I need to be able to type in a name on the front tab and for it to look up all tabs and return a discount value for that customer I searched? Make sense , I know the cells i need to include just need the structure of the formulae , can anyone help?

Thanks,
Nicola Jay.

 

[NiMip]

Hmmm I can't quite do it in one formula, but there are a couple of other ways I can think of. Let's say you're typing a name into cell B2 on a tab named "FrontTab"

1. You could put in each data tab into cell A1 (for example) something like

=IFERROR(INDEX(nameRange,MATCH(FrontTab!B2,discountRange,0)),FALSE)​

Then, where ever you like on the front tab,

=MAX('firstTabName:lastTabName'!A1)
​

2. If you don't want to go into each tab individually, you could have a list on the front tab of all the other tabs names, such as this:
[TABLE="class: grid, width: 172"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Search:[/TD]
[TD]bec[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Discount rate:[/TD]
[TD="align: right"]Formula2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sheet1[/TD]
[TD="align: center"]Formula1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sheet2[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sheet3[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Sheet4[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sheet5[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sheet6[/TD]
[TD="align: center"]""[/TD]
[/TR]
</tbody>[/TABLE]

Then, against each tab name, have discount results using:

Formula1: =IFERROR(INDEX(INDIRECT("'"&A5&"'!discountRange"),MATCH($B$2,INDIRECT("'"&A5&"'!nameRange"),0)),FALSE)​

And in B3

Formula2: =MAX(B5:B10)​

 

[Nicolajay24a]

Brilliant thanks NiMip I will give that a good go, this has been driving me mad this week !

Nicola

 

[Nicolajay24a]

I cant get this to work, bit too complicated for me. I just need a search function really the user put the company name in and it then shows on the 4 work sheets where it is. You can do the control f function to find it but is there a button to install or a more simple formula that says value that is in N119 look up all worksheets and show me the location or show me the discount in column d3:40 on all worksheets ?

Sorry for being excel dim,
Thanks Nicola.

 

[NiMip]

No that's ok, things can start to get a bit confusing once you start using things like INDIRECT.
I can't think of any other way to do it I'm afraid - maybe someone will have a better way. Or I could try to give you the exact formula you need to make it simpler for you, so you can copy and paste exactly. What are the names of your tabs, and what is the range of cells that your data sits in on each of those tabs?

 

[Nicolajay24a]

Thanks NiMip, think I have cracked it now, watched some you tube vids as well now it all makes a bit more sense I have dumbed it down for myself, had to change the data anyway as I had been mis informed the company didn't want to see the discount as for the particular product it's all the same discount it's just a case of checking an account number to see if it appears in the master list! Thanks for your time though sure I'll be back when the next question arises lol!

Thanks Nicola Jay.