# Excel - COLUMN Function Problem - Expert Solution

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
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
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
User 22/08/2018 - 06:28
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.