**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need to create a formula that fills finds the Sheet (col A) and then the Component (col B) and then adds the value I put in Col H into a cell in the corresponding cell of column of the found sheet.

Solved by Z. S. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
22/08/2018 - 06:04

Hi welcome!

User
22/08/2018 - 06:04

hello

Excelchat Expert
22/08/2018 - 06:04

Do you have a sheet to upload?

User
22/08/2018 - 06:05

uploading

[Uploaded an Excel file]

User
22/08/2018 - 06:06

allow me to recap what i need

Excelchat Expert
22/08/2018 - 06:06

Ok, I have sheet open

Excelchat Expert
22/08/2018 - 06:06

Yes, pelase

User
22/08/2018 - 06:06

its a little different than what i stated in the initial question.

User
22/08/2018 - 06:09

i need to look for be able to enter a value in COL A of the LOOKUP TAB and the formula search for the value in COL B and update a cell in COL I where that value was found

User
22/08/2018 - 06:09

FOR EXAMPLE...

User
22/08/2018 - 06:09

starting in the lookup tab sheet

User
22/08/2018 - 06:10

we see row 2

User
22/08/2018 - 06:10

15382100

User
22/08/2018 - 06:10

component "595-607"

Excelchat Expert
22/08/2018 - 06:10

Yes the first 15382100

User
22/08/2018 - 06:11

i want column h to search for column b and when it finds the value in other sheets... it will update the price (column I )

User
22/08/2018 - 06:11

with what i placed in column H

Excelchat Expert
22/08/2018 - 06:12

So you put a price in COLUMN H and lookup col B?

Excelchat Expert
22/08/2018 - 06:13

Or you put a component in col H?

User
22/08/2018 - 06:13

first one

Excelchat Expert
22/08/2018 - 06:13

Also col I is hidden?

Excelchat Expert
22/08/2018 - 06:13

Ok, a price in COL H, in the LOOKUP sheet

User
22/08/2018 - 06:14

i put a price in column H and it looks in other sheets for the VALUE in COL B and replaces prices in other sheets in COL I when that component is found

Excelchat Expert
22/08/2018 - 06:14

Which other sheets?

Excelchat Expert
22/08/2018 - 06:15

the sheet listed in COL A?

User
22/08/2018 - 06:15

correct

Excelchat Expert
22/08/2018 - 06:16

There are 3 cost columns in sheets

Excelchat Expert
22/08/2018 - 06:16

which one?

Excelchat Expert
22/08/2018 - 06:18

Also, are lookups exact or near. Near will find closest rate

Excelchat Expert
22/08/2018 - 06:20

?? where is the cost in the sheets? H3? or under current total?

User
22/08/2018 - 06:20

one moment

User
22/08/2018 - 06:20

lookups are exact

User
22/08/2018 - 06:21

price in column I

Excelchat Expert
22/08/2018 - 06:22

Col I , is same as K3?

Excelchat Expert
22/08/2018 - 06:22

Or is that just for ALUM?

User
22/08/2018 - 06:24

youre right

User
22/08/2018 - 06:24

hmmm

User
22/08/2018 - 06:24

lets use col H instead

Excelchat Expert
22/08/2018 - 06:24

ok

Excelchat Expert
22/08/2018 - 06:25

So let me restate the problem statement

Excelchat Expert
22/08/2018 - 06:25

LOOKUP table- enter price in H

Excelchat Expert
22/08/2018 - 06:26

For the TAB = COL A, find the price for component COL B

Excelchat Expert
22/08/2018 - 06:27

That price (the formula) goes in col I of the LOOKUP table (currently hiidden)

Excelchat Expert
22/08/2018 - 06:27

correct?

User
22/08/2018 - 06:28

thats sounds right

Excelchat Expert
22/08/2018 - 06:28

Price is EXACT, so if not found , enter ZERO , OR NOT FOUND

User
22/08/2018 - 06:28

lets do NOT FOUND

Excelchat Expert
22/08/2018 - 06:28

OK

Excelchat Expert
22/08/2018 - 06:29

Let me get the formula in for the first sheet. Then we should have enough time to review.

Excelchat Expert
22/08/2018 - 06:29

Please stand by

User
22/08/2018 - 06:30

i'll be patient

User
22/08/2018 - 06:30

thanks for the heko

User
22/08/2018 - 06:30

help*

Excelchat Expert
22/08/2018 - 06:39

This file is huge

User
22/08/2018 - 06:39

it is quite large

User
22/08/2018 - 06:39

lol

Excelchat Expert
22/08/2018 - 06:39

It may be easier to give you the example and the formula and we review

Excelchat Expert
22/08/2018 - 06:40

On your sheet enter 2,75 in cell H4

User
22/08/2018 - 06:40

ok

Excelchat Expert
22/08/2018 - 06:41

=INDEX('15382100'!B15:I26,MATCH( 'LOOKUP TAB'!H4,'15382100'!H15:H26,0),7)

Excelchat Expert
22/08/2018 - 06:41

Put this formula in i4

User
22/08/2018 - 06:41

ok one moment

User
22/08/2018 - 06:43

my computer is freezing up... gimme a min

User
22/08/2018 - 06:43

well... excel is anyway

Excelchat Expert
22/08/2018 - 06:43

Yes, it is quite large

User
22/08/2018 - 06:46

ok

User
22/08/2018 - 06:46

in sheet > enter h4>

User
22/08/2018 - 06:46

shet ?*

User
22/08/2018 - 06:46

sheet**

Excelchat Expert
22/08/2018 - 06:46

enter 2.75 -test number

Excelchat Expert
22/08/2018 - 06:47

=INDEX('15382100'!B15:I26,MATCH( 'LOOKUP TAB'!H4,'15382100'!H15:H26,0),7)

Excelchat Expert
22/08/2018 - 06:47

put that in i4

Excelchat Expert
22/08/2018 - 06:47

This is just to see if this is what you wanted

User
22/08/2018 - 06:49

so i entered "9999" as a test number into H4 of the LOOKUP TAB sheet

User
22/08/2018 - 06:49

in the same sheet i also entered your formula.

User
22/08/2018 - 06:50

into i4

Excelchat Expert
22/08/2018 - 06:50

We want to test the exact match first , but OK

User
22/08/2018 - 06:50

is that right or did you mean something else?

User
22/08/2018 - 06:51

i wish there was video chat haha

Excelchat Expert
22/08/2018 - 06:51

Put 2.75 in H4

User
22/08/2018 - 06:51

ok

User
22/08/2018 - 06:51

done

Excelchat Expert
22/08/2018 - 06:51

Should see 2.75 in i4

User
22/08/2018 - 06:51

correct

Excelchat Expert
22/08/2018 - 06:51

OK, test 1 is a success

Excelchat Expert
22/08/2018 - 06:52

Now change H4 to 2.70 and you should get an ERROR msg

User
22/08/2018 - 06:52

right

Excelchat Expert
22/08/2018 - 06:52

=IFERROR(INDEX('15382100'!B15:I26,MATCH( 'LOOKUP TAB'!H4,'15382100'!H15:H26,0),7),"Not Found")

Excelchat Expert
22/08/2018 - 06:53

Put that revised formula in i4 and that will generate a NOT FOUND message

User
22/08/2018 - 06:54

correct

Excelchat Expert
22/08/2018 - 06:54

Ok , that is the formula

User
22/08/2018 - 06:55

uhh..

User
22/08/2018 - 06:55

oh... one sec...

User
22/08/2018 - 06:55

lemme apply this to what i need.

Excelchat Expert
22/08/2018 - 06:56

This current formula only works with tab = 15382100

Excelchat Expert
22/08/2018 - 06:56

Problem is the 750 tabs you have

Excelchat Expert
22/08/2018 - 06:58

And also noticed you have more rates lower in the sheet?

User
22/08/2018 - 06:59

[Uploaded an Excel file]

Excelchat Expert
22/08/2018 - 06:59

Not sure what you tried to upload-got an error?

User
22/08/2018 - 07:00

one moment

User
22/08/2018 - 07:00

please goto sheet 15382100

Excelchat Expert
22/08/2018 - 07:00

ok

Excelchat Expert
22/08/2018 - 07:01

Im there

User
22/08/2018 - 07:01

i need cell H22 to update because "530-4421" was found in B22

User
22/08/2018 - 07:02

so whenever i enter a value in H4 of the LOOKUP TAB...

Excelchat Expert
22/08/2018 - 07:02

yes, you entered 2.75 and found

User
22/08/2018 - 07:03

it updates on all tabs that find that "530-4421"

Excelchat Expert
22/08/2018 - 07:03

b22= 590-4421

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*